Vlookup bug?
 

I need help in obtaining consistent vlookup function results. In most cases
i receive the accurate return. However, on some returns I get the wrong
value. The value returned belongs to the same column number but a different
row. Formula auditing does not provide where the last value comes from, so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list
created through "DATA/VALIDATION" menu. This particular line should return a
value in column 5, row 32; instead it returns data from row 22. I've changed
column number but it still returns data from row 22. What am I doing wrong?

As I read someone else say: I've been pulling my hair for the last 3 hours!

Please help!
Many thanks,
David

Vlookup bug?
 

=VLOOKUP(C23,AE5:AQ215,5,0)

"David G." wrote:

I need help in obtaining consistent vlookup function results. In most cases
i receive the accurate return. However, on some returns I get the wrong
value. The value returned belongs to the same column number but a different
row. Formula auditing does not provide where the last value comes from, so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list
created through "DATA/VALIDATION" menu. This particular line should return a
value in column 5, row 32; instead it returns data from row 22. I've changed
column number but it still returns data from row 22. What am I doing wrong?

As I read someone else say: I've been pulling my hair for the last 3 hours!

Please help!
Many thanks,
David

Vlookup bug?
 

Are you looking for an exact match, if so use

=VLOOKUP(C23,AE5:AQ215,5,0)

If you get #N/A then there is no match

--
Regards,

Peo Sjoblom





wrote in message
...
I need help in obtaining consistent vlookup function results. In most
cases
i receive the accurate return. However, on some returns I get the wrong
value. The value returned belongs to the same column number but a
different
row. Formula auditing does not provide where the last value comes from,
so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list
created through "DATA/VALIDATION" menu. This particular line should
return a
value in column 5, row 32; instead it returns data from row 22. I've
changed
column number but it still returns data from row 22. What am I doing
wrong?

As I read someone else say: I've been pulling my hair for the last 3
hours!

Please help!
Many thanks,
David

Vlookup bug?
 

Worked like a charmer. What did i tell Excel to do when i added the "0"?

"Peo Sjoblom" wrote:

Are you looking for an exact match, if so use

=VLOOKUP(C23,AE5:AQ215,5,0)

If you get #N/A then there is no match

--
Regards,

Peo Sjoblom





wrote in message
...
I need help in obtaining consistent vlookup function results. In most
cases
i receive the accurate return. However, on some returns I get the wrong
value. The value returned belongs to the same column number but a
different
row. Formula auditing does not provide where the last value comes from,
so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down list
created through "DATA/VALIDATION" menu. This particular line should
return a
value in column 5, row 32; instead it returns data from row 22. I've
changed
column number but it still returns data from row 22. What am I doing
wrong?

As I read someone else say: I've been pulling my hair for the last 3
hours!

Please help!
Many thanks,
David

Vlookup bug?
 

To look for an exact match, if you look in help you will see that they use
either
FALSE or TRUE, FALSE is equal to 0, TRUE is equal to 1 or omitted like in
your case. TRUE or omitted means the values need to be sorted in ascending
order and if it doesn't find the match it will look for the largest values
less than or equal to the lookup value whereas using FALSE or 0 it will
return #N/A if it cannot find a match



--
Regards,

Peo Sjoblom



"David G." wrote in message
...
Worked like a charmer. What did i tell Excel to do when i added the "0"?

"Peo Sjoblom" wrote:

Are you looking for an exact match, if so use

=VLOOKUP(C23,AE5:AQ215,5,0)

If you get #N/A then there is no match

--
Regards,

Peo Sjoblom





wrote in message
...
I need help in obtaining consistent vlookup function results. In most
cases
i receive the accurate return. However, on some returns I get the
wrong
value. The value returned belongs to the same column number but a
different
row. Formula auditing does not provide where the last value comes
from,
so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down
list
created through "DATA/VALIDATION" menu. This particular line should
return a
value in column 5, row 32; instead it returns data from row 22. I've
changed
column number but it still returns data from row 22. What am I doing
wrong?

As I read someone else say: I've been pulling my hair for the last 3
hours!

Please help!
Many thanks,
David

Vlookup bug?
 

The 0 represents FALSE. By default, VLOOKUP will find the closest match.
The 0 or FALSE forces VLOOKUP to find an exact match.
And like Peo said, the #N/A was an indication that there was no match. So
you may want to check that too.

HTH,
Paul


"David G." wrote in message
...
Worked like a charmer. What did i tell Excel to do when i added the "0"?

"Peo Sjoblom" wrote:

Are you looking for an exact match, if so use

=VLOOKUP(C23,AE5:AQ215,5,0)

If you get #N/A then there is no match

--
Regards,

Peo Sjoblom





wrote in message
...
I need help in obtaining consistent vlookup function results. In most
cases
i receive the accurate return. However, on some returns I get the
wrong
value. The value returned belongs to the same column number but a
different
row. Formula auditing does not provide where the last value comes
from,
so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down
list
created through "DATA/VALIDATION" menu. This particular line should
return a
value in column 5, row 32; instead it returns data from row 22. I've
changed
column number but it still returns data from row 22. What am I doing
wrong?

As I read someone else say: I've been pulling my hair for the last 3
hours!

Please help!
Many thanks,
David

Vlookup bug?
 

Thank you!
David

"PCLIVE" wrote:

The 0 represents FALSE. By default, VLOOKUP will find the closest match.
The 0 or FALSE forces VLOOKUP to find an exact match.
And like Peo said, the #N/A was an indication that there was no match. So
you may want to check that too.

HTH,
Paul


"David G." wrote in message
...
Worked like a charmer. What did i tell Excel to do when i added the "0"?

"Peo Sjoblom" wrote:

Are you looking for an exact match, if so use

=VLOOKUP(C23,AE5:AQ215,5,0)

If you get #N/A then there is no match

--
Regards,

Peo Sjoblom





wrote in message
...
I need help in obtaining consistent vlookup function results. In most
cases
i receive the accurate return. However, on some returns I get the
wrong
value. The value returned belongs to the same column number but a
different
row. Formula auditing does not provide where the last value comes
from,
so i
can't trace from there.

The formula is: =VLOOKUP(C23,AE5:AQ215,5), where C23 is a drop-down
list
created through "DATA/VALIDATION" menu. This particular line should
return a
value in column 5, row 32; instead it returns data from row 22. I've
changed
column number but it still returns data from row 22. What am I doing
wrong?

As I read someone else say: I've been pulling my hair for the last 3
hours!

Please help!
Many thanks,
David