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

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!B 2: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.
---------------------------------------------------------------------
Thanks!!
sanae6

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

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

On May 28, 4:27*pm, sanae6 wrote:
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;MA*TCH("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.
---------------------------------------------------------------------
Thanks!!
sanae6

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

Thank you!! It solved the problem.

"Pete_UK" wrote:

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

On May 28, 4:27 pm, sanae6 wrote:
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;MAÂ*TCH("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.
---------------------------------------------------------------------
Thanks!!
sanae6

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

You're welcome - thanks for feeding back.

Pete

On May 28, 4:55*pm, sanae6 wrote:
Thank you!! It solved the problem.