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?

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?

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?

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?

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

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

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