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

[CathyH]

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

 

[JE McGimpsey]

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

 

[CathyH]

That worked great - thank you so much!

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