INDEX/MATCH getting #NA on imported data sheet

[kidneythief]

Hi guys, got a little stumped here, hope someone can help. Apologies if the details are a little confusing.

So we have several Daily Records workbooks where the bulk of daily sales/inventory data input is done. The main sheets in question are:
(1) Transposed, and (2) Price List

The Transposed Sheet contains rearranged data from an exported sales report from my POS app that's transposed with VBA code.
There's two pertinent columns here: (1) POS Code & (2) Item Name

The POS Code column (Column R) is filled in with a shortened item name from the POS exported file. The Item Name column starts empty.

The Price List Sheet contains a list of products with details like item name, price, etc. It contains a column both for the POS Code item
names (Column E) as well as a column with the full item names (Column B).

I then use this basic INDEX/MATCH formula to pull the full item name from the Price List Sheet into the Item Name column on the Transposed
Sheet:

=IF(ISBLANK(R1),"",INDEX('Price List'!B:B, MATCH(R1,'Price List'!E:E,0)))

This all works as it should, but I've made a change of creating a second, external Price List workbook that all Daily Records workbooks link with via
a query so that their individual price list sheets are synced and automatically updated. (I did this through the browse for more option under
connections then importing data as a table, if that matters)

The problem is, the INDEX/MATCH formula now returns a #NA error. What could be going wrong? Did I import the data wrong perhaps? I
also tried changing the lookup arrays to the external Price List workbook itself, to no avail.

The office also has a shared OneDrive and I've tried changing the INDEX/MATCH source arrays to a Price List workbook uploaded in the cloud and in
the local OneDrive folder. Is there a better way of doing what I'm trying to do? Any leads and suggestions would be much appreciated.

Thank you!

 

[KRice]

For now, I would focus only on this part of the formula:

MATCH(R1,'Price List'!E:E,0)

as the error suggests MATCH is not finding anything. Is 'Price List'!E:E where you expect to find a value that matches R1? Have you manually found the first match in E:E (that's what MATCH will do). It sounds like E:E contains text data types. After manually finding a match, let's say in 'Price List'!E10, does =R1='Price List'!E10 return TRUE? If not, check the length of the text strings...LEN(ref) to see if there might be some non-displaying characters that need to be removed. It's difficult to say with certainty without more detail.

 

[kidneythief]

If not, check the length of the text strings...LEN(ref) to see if there might be some non-displaying characters that need to be removed.

Ah you're right, it was that simple. An update in my POS had changed the output I was drawing data from, adding odd spaces in some columns. Lol a bit embarrassing I missed that ?
Thank you for pointing me in that direction KRice!

 

[KRice]

Excellent...I'm glad you found the issue! You can probably get rid of those unwanted characters by wrapping the reference to the cell/range with TRIM or CLEAN. If that doesn't work, then you may have to dig deeper to determine the ASCII code of the offending character(s) for a more targeted removal.

 

[kidneythief]

Excellent...I'm glad you found the issue! You can probably get rid of those unwanted characters by wrapping the reference to the cell/range with TRIM or CLEAN. If that doesn't work, then you may have to dig deeper to determine the ASCII code of the offending character(s) for a more targeted removal.

Great idea, I'll incorporate TRIM/CLEAN into the Transposed Sheet! But now I'm getting grey screens, what a headache ? I think they're really pushing me to switch to 365