vlookup off by one?

[cfortran]

I have rearranged my columns so that vlookup is happy (searching the
left column only of the table argument). Here is my formula:
=vlookup(n2,b6:c6006,2,true)

I would prefer to have 'false' as the last argument, but it doesn't
see the match (although I know it is there). Could this be a floating
point problem or something? With 6000 values ranging between 8 and 80
it doesn't appear to be matching precisely. N2 for example, currently
contains 20. With the vlookup formula shown above it instead finds
19.98 (right above the right answer of 20) and returns the C column
value next to it.

I'm afraid I'm an excel dummy. It's not making much sense to me at
this point. Thanks for any help.

--cfortran

 

[Dave Peterson]

Are you sure that N2 contains 20--it doesn't contain a value that's formatted to
show 20? Another problem could be that one cell contains the text 20 ('20) and
the other contains the number 20. These values don't match.

I'd review the notes for troubleshooting =vlookup() formulas from Debra
Dalgleish:

http://contextures.com/xlFunctions02.html#Trouble


You have a response at your other thread for tips for =vlookup() and
=index(match()).

 

[Gord Dibben]

Your "matches" are not exact.

Could be decimal points are slightly off.

i.e. formatted to 2DP 1.12 looks like a match to 1.12

But stretch 1.12 out and it may be 1.1200123 which is not a match for exact 1.12

Maybe your 20 is really 20.0000123.

The TRUE argument will return the closest lower match of 19.98


Gord Dibben MS Excel MVP

 

[Tyro]

If your "right answer" of 20 is in cell B100 for example you can put this in
some empty cell: =N2=B100 If that returns TRUE, then N2 equals B100 and if
that returns FALSE, N2 is not equal to B100. It's just a quick check. Then
you can do further research. In your case it appears that N2 is not equal to
20 but is some value just under 20 such as 19.999999999 which when rounded
appears as 20 in the cell but does not equal 20 and the VLOOKUP, because of
the TRUE argument, finds the approximate value less or equal to 19.999999999
and that is 19.98 and returns the corresponding value from column C.
Expand the width of cell N2 and display more places of decimal to see what
is there.

Tyro

 

[OssieMac]

Not sure if I am reading your question correctly but I get the feeling that
you are copying that formula down a column and if this is correct, then the
lookup range should be absolute with the $ signs.

=vlookup(n2,$b$6:$c$6006,2,true)