Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I only get valid "answers" in a VLOOKUP function (no #N/A's
I use a lot of VLOOKUP functions in formulas and am frustrated by the #N/A
results whenever the value I'm looking for isn't in the reference list range. Is there a simple way to have a formula leave the result blank if the value isn't found? |
#2
|
|||
|
|||
Hi!
One way: =IF(COUNTIF(A1:A100,lookup_value),VLOOKUP(lookup_v alue,A1:B100,2 0),"") Biff "SWEdwards" wrote in message ... I use a lot of VLOOKUP functions in formulas and am frustrated by the #N/A results whenever the value I'm looking for isn't in the reference list range. Is there a simple way to have a formula leave the result blank if the value isn't found? |
#3
|
|||
|
|||
Use the general form:
=IF(ISNA(VLOOKUP()),"",VLOOKUP()) -- Gary''s Student "SWEdwards" wrote: I use a lot of VLOOKUP functions in formulas and am frustrated by the #N/A results whenever the value I'm looking for isn't in the reference list range. Is there a simple way to have a formula leave the result blank if the value isn't found? |
#4
|
|||
|
|||
Perfect.
Thanks so much! "Gary''s Student" wrote: Use the general form: =IF(ISNA(VLOOKUP()),"",VLOOKUP()) -- Gary''s Student "SWEdwards" wrote: I use a lot of VLOOKUP functions in formulas and am frustrated by the #N/A results whenever the value I'm looking for isn't in the reference list range. Is there a simple way to have a formula leave the result blank if the value isn't found? |
#5
|
|||
|
|||
Hi,
try to use ISERROR function: =IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;V LOOKUP(A1;Sheet1!A2:C11;2;0)) Marian http:\\skolenieexcel.host.sk |
#6
|
|||
|
|||
Hi!
While both your suggestion and Gary"s "work", neither is as efficient as my suggestion. Why execute 2 lookups when the lookup_value is present? Using Countif to verify that the lookup_values exsists is faster than using IF(ISERROR or IF(ISNA. Biff wrote in message ups.com... Hi, try to use ISERROR function: =IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;V LOOKUP(A1;Sheet1!A2:C11;2;0)) Marian http:\\skolenieexcel.host.sk |
#7
|
|||
|
|||
How do I only get valid "answers" in a VLOOKUP function (no #N/A's
I remember reading a couple of years ago that a speed test showed that an
ISNA plus Match() combination was a more efficient error check then Countif(), while either one of them was definitely much faster then a double Vlookup(). -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! While both your suggestion and Gary"s "work", neither is as efficient as my suggestion. Why execute 2 lookups when the lookup_value is present? Using Countif to verify that the lookup_values exsists is faster than using IF(ISERROR or IF(ISNA. Biff wrote in message ups.com... Hi, try to use ISERROR function: =IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;V LOOKUP(A1;Sheet1!A2:C11;2; 0)) Marian http:\\skolenieexcel.host.sk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
Using VLOOKUP for one option, if this option is not valid than sec | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |