If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VLOOKUP returns incorrect data
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? |
#2
|
|||
|
|||
VLOOKUP returns incorrect data
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) -- Jacob (MVP - Excel) "burtlake" wrote: 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? |
#3
|
|||
|
|||
VLOOKUP returns incorrect data
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 "burtlake" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|