ELIMINATE #N/A

  • Thread starter Thread starter lehigh
  • Start date Start date
L

lehigh

Hi All

I'm using the following formula

=VLOOKUP(B20,CONTACTS!$A$3:$AH$1019,7,0)

If the subject in B20 dosen't exist, it returns #N/A

How can I re-write this to return a blank?


Thank you for your help

Tom Snyder
 
=IF(ISERROR(MATCH(B20,$A$3$A$1019)),"",VLOOKUP(B20,CONTACTS!$A$3:$AH$1019,7,0))

Untested

Dan E
 
make it part of an if statement, returning a "" if b20=""

IF(B20<>"",VLOOKUP(B20,CONTACTS!$A$3:$AH$1019,7,0),"")
 
Sorry I jumped too quick on this one. 'Doesn't exist' means more than 1
thing here and I answered it one way. The other way, if it doesn't exist in
the lookup range, is something else.

I don't know if it would work to use an IF there, but if the value returned
= #N/A then return "", else repeat the vlookup and return what vlookup
returns.
 
One way,

=IF(COUNTIF(CONTACTS!$A$3:$A$1019,B20),VLOOKUP(B20,CONTACTS!$A$3:$AH$1019,7,
0),"")
 
Dan:

I tried your formula.
It eliminated the #N/A, but did not return any names if they do exist.
I thought you may have fogotten CONTACTS! , so I entered it ito the
formula and it returned the names, but the #N/A came back
Here is the modified formula:

=IF(ISERROR(MATCH(B20,CONTACTS!$A$3:$A$1019)),"",VLOOKUP(B20,CONTACTS!$A$3:$AH$1019,7,0))

Am I still doing something wrong?

Thanks

Tom Snyder
 
Tom,

You were correct that I forgot the CONTACTS!, but when I tested
your version of the formula it did return a value from column G (7)
of the contacts sheet?

Dan E
 
=IF(ISNA(MATCH(B20,Contacts!$A$3:$A$1019,0)),"",VLOOKUP(B20,Contacts!$A$3:$AH$1019,7,0))

I also forgot the 0 in the match (MATCH(val, range, 0))

Dan E
 
Back
Top