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