return N/A when no first match but do nothing if no second match
 

This is the formula I'm working with:

=OFFSET(INDIRECT("Canadianl!A"&MATCH($O$1,Canadian !$A:$A,0)),7,3)+(OFFSET(INDIRECT("US!A"&MATCH($O$1 ,US!$A:$A,0)),7,3)*$O$2)

In the worksheet called "Canadian" there must be an exact match or it should
return #N/A, but in the sheet called "US" there will not always be a match.

So what I need is: sum the values from "Canadian" and "US" if they both
match, but if there is no match in US return only the value from "Canadian".

Hope that makes sense!

Cathy

return N/A when no first match but do nothing if no second match
 

One way:

=IF(ISNA(MATCH($O$1, US!$A:$A, FALSE)), 0, INDEX(US!$A:$C,
MATCH($O$1, US!$A:$A, FALSE)+7,3)) + INDEX(Canadian!$A:$C,
MATCH($O$1,Canadian!$A:$A, FALSE)+7, 3) * $O$2



In article ,
CathyH wrote:

This is the formula I'm working with:

=OFFSET(INDIRECT("Canadianl!A"&MATCH($O$1,Canadian !$A:$A,0)),7,3)+(OFFSET(INDI
RECT("US!A"&MATCH($O$1,US!$A:$A,0)),7,3)*$O$2)

In the worksheet called "Canadian" there must be an exact match or it should
return #N/A, but in the sheet called "US" there will not always be a match.

So what I need is: sum the values from "Canadian" and "US" if they both
match, but if there is no match in US return only the value from "Canadian".

Hope that makes sense!

Cathy

return N/A when no first match but do nothing if no second mat
 

That worked great - thank you so much!

"JE McGimpsey" wrote:

One way:

=IF(ISNA(MATCH($O$1, US!$A:$A, FALSE)), 0, INDEX(US!$A:$C,
MATCH($O$1, US!$A:$A, FALSE)+7,3)) + INDEX(Canadian!$A:$C,
MATCH($O$1,Canadian!$A:$A, FALSE)+7, 3) * $O$2



In article ,
CathyH wrote:

This is the formula I'm working with:

=OFFSET(INDIRECT("Canadianl!A"&MATCH($O$1,Canadian !$A:$A,0)),7,3)+(OFFSET(INDI
RECT("US!A"&MATCH($O$1,US!$A:$A,0)),7,3)*$O$2)

In the worksheet called "Canadian" there must be an exact match or it should
return #N/A, but in the sheet called "US" there will not always be a match.

So what I need is: sum the values from "Canadian" and "US" if they both
match, but if there is no match in US return only the value from "Canadian".

Hope that makes sense!

Cathy