Connection Data

[NPandelos]

I'm having an issue with a connection to an outside database. I used a Legacy Wizard- from Microsoft Query (Legacy) to pull in data from our ERP system. The data I pull in is fine, the issue I am having is my Xlookup is not finding a specific set of numbers.
Below are the numbers in question. This is coming from the connection data.

My xlook up is not recognizing any of them

It isn't until I put the curser in a cell then hit "Enter" the formula will recognize it and it does this shift in the cell.

As soon as I refresh the data it reverts back.

I have been racking my brain trying to figure out what is going on. Any ideas?

 

[Trebor76]

Hi NPandelos,

In Excel numbers are naturally right aligned and text (strings) are left aligned so Excel is regarding the stock code reference as a string. This is presumably because the field in the Db is set as text.

See if using the VALUE function helps like I have done in the following example (you will obviously have to change the ranges to suit your data):

=XLOOKUP(VALUE(H4),B5:B9,E5:E9)

Regards,

Robert

 

[NPandelos]

Awesome! Thank you. That worked.

 

[Trebor76]

[Awesome! Thank you. That worked.]

Thanks for letting us know and you're welcome