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

S

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.
 
P

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
 
S

sanae6

Thank you!! It solved the problem.

Pete_UK said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top