Formula Lookup returns wrong value

[RisingPhoenix]

The formula Lookup keeps returning the cell value in the row above the one that I want.
Does anyone know how to fix it?

Regards

 

[StreetMan]

The formula Lookup keeps returning the cell value in the row above the one that I want.
Does anyone know how to fix it?

This was happening to me. I changed the formula from TRUE to FALSE after making sure the fields I was matching...actually matched. They did not, due to several "spaces" after the text. Couldn't see em...but they were there and the TRUE made it look for something close...like the field just above it!!!

 

[mattmickle]

you should not need to sort your data at all if you're using the VLOOKUP formula with a False indicator.

=vlookup(REFCELL, DATA RANGE, COLUMN, FALSE)

I use this formula just about every minute of every work day with no issues.

 

[kaushal101]

The range lookup seaches must be sorted (ascending). If the lookup function does not find the value to be looked up, it will return the value just before it.

It sounds to me like the value you are looking for and the value in your lookup range might be just a bit different (maybe a trailing space?).

Whenever I do lookups, I like to nest them:

=IF(LOOKUP(C4,F4:F14,F4:F14)=C4,LOOKUP(C4,F4:F15,G4:G14),"Not Found")

Where C4 is the value to search for, F4:F14 is the search range and G4:G14 is the return range.

Hope this helps,

K

Excellent. Additional trailing space (due to copy of data) was the problem. Thanks for the tip.