Feb 17 2022 06:47 AM
Hi, I have a problem with LOOKUP (WYSZUKAJ in Polish version). I have a long list of part names with manufacturer name, category, subcategory, addidtional parameter and set of prices in every line. On another sheet I want to type just a part name and get coresponding price. I was trying to use LOOKUP (and vertical and horizontal) and always get random values. While testing it I discovered, that even for small set of data it doesn't work as I expected.
Below you can see six tables which are just first table with sequent values typed in blue cell. In yellow cell I expected result as in the first or third table. Of course, all references are valid. You can also see formula which is the same in all yellow cells regarding shift of those cells.
What should I do to achieve expected results? Typing Four in blue cell should give '4' in yellow cell.
There is no data type mismatch.
Best regards,
ZiKE
Feb 17 2022 07:33 AM - edited Feb 17 2022 07:34 AM
The issue is the words are not sorted in ascending order so the LOOKUP fails.
From Microsoft:
Important: The values in array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
A better option is to use VLOOKUP or XLOOKUP (If available).
Feb 17 2022 07:44 AM - edited Feb 17 2022 07:46 AM
SolutionThere are like 4 different 'lookup' version and all except the newest XLOOKUP default to expecting the array to be in ascending order. Either use XLOOKUP if you have it, or use VLOOKUP and define the range_lookup to be 'False' to only find an exact match, or use INDEX(... MATCH(.... , 0) ) and note the MATCH must have that last parameter set to find a exact match also.
Feb 17 2022 07:44 AM - edited Feb 17 2022 07:46 AM
SolutionThere are like 4 different 'lookup' version and all except the newest XLOOKUP default to expecting the array to be in ascending order. Either use XLOOKUP if you have it, or use VLOOKUP and define the range_lookup to be 'False' to only find an exact match, or use INDEX(... MATCH(.... , 0) ) and note the MATCH must have that last parameter set to find a exact match also.