Get LOOKUP() Function To Only Look Up Exact Values

**JonnyBoy333** · 10-20-2011, 06:14 PM

I've been using the LOOKUP() function and like it a lot only it has one feature I don't want. This one:

If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

As I am working with text string instead of numbers this has resulted in a number of incorrect values being returned. I would like it if it could not find an exact match it would just return an error or say "0", and not try to find a close match. Anyone know a fix for this?

**ConneXionLost** · 10-20-2011, 06:20 PM

If you use a "0" or "FALSE" as the range_lookup value, it will return NA# if there is no exact match.

Edit - Oops, just noticed you wrote LOOKUP instead of VLOOKUP. Sorry!

**ConneXionLost** · 10-20-2011, 06:25 PM

Along those lines... Have you tried VLOOKUP or INDEX/MATCH?

**JonnyBoy333** · 10-21-2011, 03:05 PM

I have not, could you explain how you would use those in my situation?

I attached an example worksheet. The green cells are the one's that need to be filled by searching the 'Imported' sheet for the right sku # and returning the corresponding upc #. If it can't find the right number it should return an error, not the nearest value.