Vlookup bug?

[Guest]

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

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[PCLIVE]

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

 

[Guest]

Thank you!
David

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