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
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 therefor, VLOOKUP won’t return a #N/A!
By Juan Pablo Gonzalez on 17-Jan-2002