Return Zero instead of #N/A

January 17, 2002 - by Juan Pablo Gonzalez

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 therefore, VLOOKUP won’t return a #N/A!