VLOOKUP not returning correct value
 

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

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

----


leigh wrote:
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

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


On Thu, 13 Oct 2005 10:29:34 GMT, leigh
wrote:

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

----


leigh wrote:
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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Many thanks for that.

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

"FALSE" sorted it.

Cheers
LH

Richard Buttrey wrote:
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


On Thu, 13 Oct 2005 10:29:34 GMT, leigh
wrote:


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

----


leigh wrote:

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


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

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



leigh wrote:

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

----


leigh wrote:

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

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

Jerry

leigh wrote:

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

----


leigh wrote:

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

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.

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



Richard Buttrey wrote:

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


On Thu, 13 Oct 2005 10:29:34 GMT, leigh
wrote:



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

----


leigh wrote:


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



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________