How to replace "#N/A" w "0"when vlookup couldn't find the match?

L

LarryLL

Holly said:
I try not to show "#N/A" when my vloopup couldn't find the match on th
report.
Click to expand...

One way is to use the ISERR function.

IF(ISERR(VLOOKUP(...),0,VLOOKUP(...)
 
J

JE McGimpsey

Did you try it? That won't work at all, since ISERR will always return
FALSE when passed #N/A!

From XL Help:
ISERR
Returns the logical value TRUE if value refers to any error value
except #N/A; otherwise it returns FALSE.
Click to expand...


One could use ISERROR(), which does trap #N/A, but that would mask other
errors as well. Better to use ISNA

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

though out of sheer cussedness I prefer the slightly more efficient

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))
 
Top