Extracted from MrExcel.com Message Board

One common issue I face is "How can I have VLOOKUP return a 0 or a empty string "" instead of #N/A! ?"

Let's assume that the current formula is

=VLOOKUP(A1,$B$2:$D$100,3,False)
or more condensed
=VLOOKUP(A1,$B$2:$D$100,3,0)

one approach to do this is as follows

=IF(ISNA(VLOOKUP(A1,$B$2:$D$100,3,0)),””, VLOOKUP(A1,$B$2:$D$100,3,0))

but this requires Excel calculating TWICE the VLOOKUP formula which is “expensive” in terms of efficiency.

One improved method is

=IF(COUNTIF($B$2:$B$100,A1), VLOOKUP(A1,$B$2:$D$100,3,0),””)

This way the VLOOKUP is only calculated if the value in A1 exists in B2:B100, and therefor, VLOOKUP won’t return a #N/A!