VLOOKUP returns incorrect data

B

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?
 
J

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:D,2,FALSE)

OR

=VLOOKUP(A1,Sheet2!A:D,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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top