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
or more condensed
One approach to do this is as follows
But this requires Excel calculating TWICE the VLOOKUP formula which is “expensive” in terms of efficiency.
One improved method is:
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!