Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 __________________________ |
#4
|
|||
|
|||
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 __________________________ |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP not returning results | Excel Worksheet Functions | |||
VLOOKUP seems correct, still getting #N/A | Excel Worksheet Functions | |||
Vlookup, What is correct formula for problem below? | Excel Worksheet Functions | |||
Vlookup not returning correct value | Excel Worksheet Functions | |||
MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. | Excel Discussion (Misc queries) |