How can I automatically replace the error #N/A with 0 in Excel?

[sanae6]

Hi,

I am trying to use OFFSET and MATCH to locate values in a unsorted table and
perform simple math with these values such as SUM.

When MATCH returns #N/A error value since what I am looking for does not
exist in the unsorted table, I would like the MATCH formula to return 0
(ZERO) so that the total formula of SUM does not also return #N/A. Could
someone help?

------------------------------------------------
Example:
=OFFSET(Sales!A1;MATCH("USASales";Sales!B2:B50;0);3;1;1)+OFFSET(Sales!A1;MATCH("CanadaSales";Sales!B2:B50;0);3;1;1)

Returns #N/A because "CanadaSales" does not exist in the worksheet "Sales"
from B2 to B50.

I still would like this formula to return the value of USASales, regarding
CanadaSales as zero or somehow ignoring the #N/A error.

 

[Pete_UK]

Try this:

=IF(ISNA(MATCH("USASales";Sales!B2:B50;0));0 ;OFFSET(Sales!
A1;MATCH("USASales";Sales!B2:B50;0);3;1;1))+
IF(ISNA(MATCH("CanadaSales";Sales!B2:B50;0));0 ;OFFSET(Sales!A1;MA­
TCH("CanadaSales";Sales!B2:B50;0);3;1;1))

Be wary of spurious line-breaks in the newsgroups which sometimes
introduce a hyphen character - I've put a few spaces in to try to
avoid this, but it is all one formula.

Hope this helps.

Pete

 

[sanae6]

Thank you!! It solved the problem.

Try this:

=IF(ISNA(MATCH("USASales";Sales!B2:B50;0));0 ;OFFSET(Sales!
A1;MATCH("USASales";Sales!B2:B50;0);3;1;1))+
IF(ISNA(MATCH("CanadaSales";Sales!B2:B50;0));0 ;OFFSET(Sales!A1;MA­
TCH("CanadaSales";Sales!B2:B50;0);3;1;1))

Be wary of spurious line-breaks in the newsgroups which sometimes
introduce a hyphen character - I've put a few spaces in to try to
avoid this, but it is all one formula.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete