*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!

By Juan Pablo Gonzalez on 17-Jan-2002