VLOOKUP returns incorrect data

[burtlake]

I am using VLOOKUP to determine addresses from a master list. However I have
found that if the Lookup Value is NOT in the Table Array, I will get
incorrect information.

For example if the Table Array has entries for Schmidt and Schultz, and I
attempt to lookup Schnell (which is not in the Table Array), I will get the
value for Schultz, which is incorrect.

How can this be avoided? Or, how can I get a message saying the Lookup
Value was not found - rather than getting incorrect results?

 

[Jacob Skaria]

You would have missed out the last argument of VLOOKUP() Range_lookup which
is a logical value that specifies whether you want VLOOKUP to find an exact
match or an approximate match. If TRUE or omitted, an exact or approximate
match is returned. If an exact match is not found, the next largest value
that is less than lookup_value is returned..

=VLOOKUP(A1,Sheet2!A,2,FALSE)

OR

=VLOOKUP(A1,Sheet2!A,2,0)

If no entry is found it returns NA# . To handle this use IF() ISNA()
combination as below...Replace vlookup_formula string with your actual formula

=IF(ISNA(vlookup_formula),"Not Found",vlookup_formula)

 

[Ashish Mathur]

Hi,

Try this

=if(iserror(vlookup(A3,B4:100,2,0)),"",vlookup(A3,B4:100,2,0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com