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

I try not to show "#N/A" when my vloopup couldn't find the match on the report.

Originally Posted by Holly

I try not to show "#N/A" when my vloopup couldn't find the match on the report.

One way is to use the ISERR function.

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

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.


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))

In article <[email protected]>,
LarryLL <[email protected]> wrote:

> Holly Wrote:
> > I try not to show "#N/A" when my vloopup couldn't find the match on the
> > report.
>
> One way is to use the ISERR function.
>
> IF(ISERR(VLOOKUP(...),0,VLOOKUP(...))