VLOOKUP - return 0 instead of "#N/A"

[Guest]

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

 

[Dave Peterson]

=if(iserror(vlookup(...)),0,vlookup(...))

In xl2007:

=iferror(vlookup(...),0)

 

[Guest]

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",

 

[Guest]

=IF(ISERROR(VLOOKUP([your criteria])),0,VLOOKUP([your criteria]))

"IF the VLOOKUP returns an error, THEN 0, ELSE do the VLOOKUP."

Dave