View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JLatham JLatham is offline
external usenet poster
 
Posts: 2,203
Default VLOOKUP - return 0 instead of "#N/A"

Nest it with an IF with a test for the #NA condition like this
=IF(ISNA(VLOOKUP(A1,range,col,param),0,VLOOKUP(A1, range,col,param))
What that says is test if the lookup will cause #NA, and if it will then
display 0 (zero) else go ahead and perform the VLOOKUP for real and display
its result.

The zero doesn't even have to be a zero, in other conditions you could put a
custom phrase there such as ,"No Match Found",

"EDCNB" wrote:

When using VLOOKUP to find an exact match for a value, and if there is no
exact match, the formula will return "#N/A". How should I modify the formula
to make it return the number 0 instead?

Thanks