| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
| MATCH 3 COLUMS RETURN 4TH | Excel Discussion (Misc queries) | |||
| MATCH 3 COLUMS RETURN 4TH | Excel Discussion (Misc queries) | |||
| match a name and return a tel number? | Excel Discussion (Misc queries) | |||
| Match/Index return #N/A | Excel Worksheet Functions | |||
Linear Mode
