Find and Replace #N/A with 0

[David Greene]

After a vlookup etc, isn't there a way of simply changing unmatched
or #N/A to 0 so the addition formulas are still good. this would be a
great tool to have. thanks.

 

[CyberTaz]

One option - You can probably do so if you combine an IF function with the
ISERROR function with your error-prone function nested within:

=IF((ISERROR,YourFunction),0,YourFunction)

HTH |:>)

 

[JE McGimpsey]

One option - You can probably do so if you combine an IF function with the
ISERROR function with your error-prone function nested within:

=IF((ISERROR,YourFunction),0,YourFunction)

just a consideration:

In general it's better when designing spreadsheets to trap only the most
limiting error. In addition to #N/A, ISERROR() will return 0 for the
#VALUE!, #REF!, #DIV/0!, #NUM!, and #NAME errors. This could lead to
trouble if, say, the user inadvertently deleted the enough columns in
the lookup range to cause VLOOKUP to return a #REF! - the
IF(ISERROR(...),0,...) construction would return 0 for all further
calculations without any indication that there's an error.].

I'd recommend trapping only #NA, using:

=IF(ISNA(VLOOKUP(x,rng,FALSE)), 0, VLOOKUP(x,rng,FALSE))

Additionally, while computer and calculation speed have reduced the need
for efficiency, in a calculation intensive worksheet, I would use
MATCH() for the ISNA() test, since there's no reason to return the value
from the other column during the test:

=IF(ISNA(MATCH(x,rng1,FALSE)),0,VLOOKUP(x,rng,FALSE))

where rng1 is the first column of rng.

 

[CyberTaz]

Excellent point - I didn't consider trapping just the #N/A.

Regards |:>)

One option - You can probably do so if you combine an IF function with the
ISERROR function with your error-prone function nested within:

=IF((ISERROR,YourFunction),0,YourFunction)

just a consideration:

In general it's better when designing spreadsheets to trap only the most
limiting error. In addition to #N/A, ISERROR() will return 0 for the
#VALUE!, #REF!, #DIV/0!, #NUM!, and #NAME errors. This could lead to
trouble if, say, the user inadvertently deleted the enough columns in
the lookup range to cause VLOOKUP to return a #REF! - the
IF(ISERROR(...),0,...) construction would return 0 for all further
calculations without any indication that there's an error.].

I'd recommend trapping only #NA, using:

=IF(ISNA(VLOOKUP(x,rng,FALSE)), 0, VLOOKUP(x,rng,FALSE))

Additionally, while computer and calculation speed have reduced the need
for efficiency, in a calculation intensive worksheet, I would use
MATCH() for the ISNA() test, since there's no reason to return the value
from the other column during the test:

=IF(ISNA(MATCH(x,rng1,FALSE)),0,VLOOKUP(x,rng,FALSE))

where rng1 is the first column of rng.