VLOOKUP not returning correct value

L

leigh

All,

When I run vlookup on a selected range of data, then answers returned to
me are from the cell directly above.

ie:-

a 10 1
b 20 2
c 30 3

if the above date was my range ("data") and I use the formula:-
=vlookup(a1,data,2)

I put "b" in cell a1 and it will return me the data for row a,10,1

why would it do that ?!?!?

LH
 
L

leigh

Just checked this and it works.

I have it running on a bigger table - only 150 lines and I have the problem.

Any suggestions ??

LH
 
R

Richard Buttrey

Probably because your data isn't sorted on the column which contains
the a,b,c etc. Check the help to understand the way Vlookup works. It
finds the next highest value and drops back one row. I suspect your
data has a "c" higher up in the list than a "b" and the row before the
"c" row is an "a" row. Hence the formula finds the row higher than
your test value b, and drops back one row,

If you want to run a lookup on an unsorted database, you need to
include the "False" bit in the formula. i.e.

=VLOOKUP(A1,data,2,FALSE)

I generally find that the for most real world examples of Vlookup, the
False bit is vital and should perhaps be the default.


Rgds


Just checked this and it works.

I have it running on a bigger table - only 150 lines and I have the problem.

Any suggestions ??

LH
Click to expand...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
L

leigh

Many thanks for that.

I found that all of my user names had a space after them.....

"FALSE" sorted it.

Cheers
LH
 
R

Roger Govier

Hi Leigh

It depends whether all of your Lookup column is sorted.
If it is you should get the correct result, or the values in the cell
that has a lower value than the looked up value when the 4th argument to
VLOOKUP isn't used.
=vlookup(a1,data,2,0) with the 4th argument as 0 of FALSE will deal with
unsorted data and looks for an exact match else it returns the #N/A value.

Regards

Roger Govier
 
J

Jerry W. Lewis

Perhaps the value in the table is "b " instead of "b"? What does
=VLOOKUP(a1,data,2,FALSE) return?

Jerry
 
R

Roger Govier

Hi Richard
I generally find that the for most real world examples of Vlookup, the
False bit is vital and should perhaps be the default.
Click to expand...

I quite agree. The assumption that the absence of the use of the 4th argument defaults to True is a nuisance.
Now, I put 0 in every time I enter a Vlookup regardless, and only change it to 1 on those few occasions when required.
I'm sure there is a valid reason, and maybe Vlookup works faster without the 4th argument, and will obviously be faster when the data is sorted, but life (and data sets) never seem to work out that conveniently.

Regards

Roger Govier
 
Top