Lookup Index/Match Formula is not working.

[Excel987]

I am trying to perform an Order lookup from the "Lookup Orders" sheet. I am referencing the "Order Text" field located in column "R" on the "Orders" sheet to MATCH the "Select Order No." on the "Lookup Orders" sheet.

For some reason, I am getting a #NA error when I am trying to find a match. I have validated that the Index/Match formula on the "Lookup Order" sheet is working because if I replace the formula that I am using with the actual number "23120101" (which is the first order number) directly in column R on the Orders sheet, it finds the match when I type in "23120101" on the "Lookup Order" sheet, but if I stay with the formula in column R on the "Orders" sheet, it doesn't find the match.

I have uploaded a working sample worksheet for your reference. Thanks!

[MrShorty]

You are asking Excel to perform an exact match lookup (3rd argument of MATCH() is 0), and, when Excel looks for an exact match, the match must be exact. I notice that the value in Orders! column R is the result of a CONCATENATE() function, so the result will be text (verify with an ISTEXT() function). ISTEXT(C6) on sheet Lookup Orders says that this entry is a real number. The number 23120101 is not exactly the same as the text string "23120101." Because they are not exactly the same, MATCH() returns NA#.

The solution is to decide if you are going to store these numbers as numbers or text, then be consistent throughout the sheet. If you want them to be numbers in "Order", nest the CONCATENATE() function inside of a VALUE() function (or equivalent operation) to force the value to be a number. If you want to enter text strings in "Lookup Orders," put a leading apostrophe on each entry in C6, or preformat the cell as text @ or other strategy for forcing text input to the MATCH() function.

[TMS]

Or you can do this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Excel987]

Thanks, TMS, this fixed my look up problem. How did adding &"" fixed the issue? Again, thanks for fixing my issue, it is very appreciated.

[TMS]

You're welcome. Thanks for the rep.

How did adding &"" fixed the issue?

It concatenates a null value to the original numeric search value, thus converting it to text. You are then matching text with text.