vlookup is returning a value one cell above the correct cell.

[dbaker4]

i have a simple vlookup formula in one worksheet referenceing values in
another worksheet, ie ' =vlookup(B2,otherworksheet!A237,3) '

the data has been sorted, but the vlookup function continues to return a
value from 'otherworksheet' that is physically one cell above the corect
cell. for example, if the value in B2 is found in row 23 of
'otherworksheet', i am getting the value from row 22 in 'otherworksheet'. i
have never had a problem like this. i have resorted the data several times
(ascending) and tried the regular lookup function, but i keep getting the
value ove cell above the target cell. thoughts?

 

[Peo Sjoblom]

If the value is not exact it will return the next largest value that is less
than lookup using your setup, if you are only looking for exact matches use
FALSE or 0

=vlookup(B2,otherworksheet!A237,3,FALSE)

or

=vlookup(B2,otherworksheet!A237,3,0)

then it will return an error if not exact match is found and the lookup
range need not to be sorted


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[dbaker4]

the lookup value, or B2, is actually a three letter equity ticker (MMM for 3M
Company). there is only one equity ticker per public company in the table
array. the ' =vlookup() ' formula is successfully finding the corresponding
ticker in the 'otherworksheet' but for every ticker the equation is returning
a value from one row above the correct cell. it is happening for every
ticker (~125 tickers) and the problem is the same. therefore, there is some
sort of systematic error. when i try to use 'FALSE' or '0' as the range
lookup, i get the #N/A response. however, the tickers in the lookup value
and in the reference sheet are the exact same, so the equation should find a
value when i enter 'FALSE' or '0'...

 

[Peo Sjoblom]

If you get an error using FALSE then they are not exact, you can easily test
that be comparing them

=EXACT(A1,C1)

or even

=A1=C1

will return TRUE if they are. All it takes is an extra space or some html
crap if imported from a web page or intranet. If the formula returns true
then you must have messed up the table dimensions

you should always use FALSE if you are looking for exact matches, if not you
will get erroneous data, I prefer never to use TRUE (which is what you do
when leaving it empty) with text, it is very handy when it comes to numbers
when you are looking for exact or closest smaller value.


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Matt]

I have the same problem. I'm matching imprecise text and it's finding the
right match but returning a cell above. I've decided to fix it by hand since
the program appears to be inflexible in this situation. I just added another
column and made it equal the cell below.