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_value,A1:B100,2 0),"")

Biff

One way:

=IF(COUNTIF(A1:A100,lookup_value),VLOOKUP(lookup_value,A1:B100,2 0),"")

Biff

Use the general form:

=IF(ISNA(VLOOKUP()),"",VLOOKUP())

--

Gary''s Student

=IF(ISNA(VLOOKUP()),"",VLOOKUP())

--

Gary''s Student

Hi,

try to use ISERROR function:

=IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;VLOOKUP(A1;Sheet1!A2:C11;2;0))

Marian

http:\\skolenieexcel.host.sk

try to use ISERROR function:

=IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;VLOOKUP(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

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

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 !

---------------------------------------------------------------------------

my

using

=IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;VLOOKUP(A1;Sheet1!A2:C11;2;

0))

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 !

---------------------------------------------------------------------------

my

using

=IF(ISERROR(VLOOKUP(A1;Sheet1!$A$2:$C$11;2;0));0;VLOOKUP(A1;Sheet1!A2:C11;2;

0))

1. My I's are coming up i's. (lower case)

2. Why do I get correct answers to formula then #NAME for some no's

3. Answer No and have it fill in No's in other fields

4. How to find serial nos (unique H/W nos) of peripherials , any devices in a PC

5. How can I stop Merged nos. from excel having 6-7 nos after dec

6. How can I stop Merged nos. from excel having 6-7 nos after decimal

7. how do I add new rows when doing vlookup function?

8. Doc. Page Nos. vs Sec. Page Nos.

9. Alignment of bracketed negative nos. with positive nos. in table

10. how to find nos. of each day in certain nos. of days?

11. IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

12. Getting a cell address from Vlookup function in a macro?

13. I'm not getting the correct answer while using ilaplace function

14. getting different answers to the same function

15. Vlookup Using VBA (without using VLOOKUP function)

6 post • Page:**1** of **1**