Changing NA to an Actual Response Text

May 09, 2002 - by Ian Loughlin

First off, a special nod to MrExcel for giving a hack like me a shot or two as guest daily tipster. Let me preface this by saying most everything I learned about excel came from this great site, so don’t be surprised (or accuse me of theft) if things look familiar. To the tip:

VLOOKUP is a powerful tool. Sometimes, if an exact match is not found, a value other than the standard #N/A is desirable. For example, if your look up range (table_array) is A1:C10, the formula

=VLOOKUP("cat",$A$1:$C$10,3,FALSE)

will return #N/A if cat is not found in A1:A10. If instead you wanted to return the phrase “No Match”, the following formula is often suggested:

=IF(ISNA(VLOOKUP("cat",$A$1:$C$10,3,FALSE)),"no match",VLOOKUP("cat",$A$1:$C$10,3,FALSE))

This functions properly, but at the expense of having to lookup your value twice. This can be improved by the following:

=IF(COUNTIF($A$1:$A$10,"cat")>0,VLOOKUP("cat",$A$1:$C$10,3,FALSE),"no match")

or with even less carpal tunnel risk by recognizing excel treats zero and “False” identically:

=IF(COUNTIF($A$1:$A$10,"cat"),VLOOKUP("cat",$A$1:$C$10,3,0),"no match")

IML