Formula returns array rather than value

[excelmonky12345]

I'm trying to SUMIF values in another sheet (therefore the indirect), however the keys are different so I need to map the keys (therefore the Index Match)).

Suppose there are two sheets, sheetA and sheetB and sheetC

sheetA is below, I'm trying to fill out Total Money

Person	Total Money
Tim
Bob

sheetB is as follows:

Person	Money
Timothy	5
Robert	1
Robert	1
Timothy	5

Sheet C is a map

Key1	Key2
Timothy	Tim
Robert	Rob

Essentailly, in my model I have a ton of sheetB's and only 1 sheetA, so we must map the keys in sheetB to correspond to sheetA (rather than vice versa). And I'm using a formula to write many indirects. But for the sake of example,

=SUMIFS(INDIRECT(F2),INDEX(sheetC!$B$2:$B$3,MATCH(INDIRECT(E2),sheetC!A2:A3,0)),A2)

The above formula should return a value, but it instead returns an array and I'm really flabbergasted as to why.

The problem is the indirect in the index match formula, if we seperate that out and do:

=SUMIFS(INDIRECT(F2),E12#,A2)

Where E12# is the formula: =INDEX(sheetC!$B$2:$B$3,MATCH(INDIRECT(E2),sheetC!A2:A3,0))

The formula works perfectly fine. But this just confuses me more, because all I've done is broken the formula into two parts.

Does anyone understand why it might not be working as we might want?

 

[dreid1011]

I'm trying to SUMIF values in another sheet (therefore the indirect), however the keys are different so I need to map the keys (therefore the Index Match)).

Suppose there are two sheets, sheetA and sheetB and sheetC

sheetA is below, I'm trying to fill out Total Money

Person	Total Money
Tim
Bob

sheetB is as follows:

Person	Money
Timothy	5
Robert	1
Robert	1
Timothy	5

Sheet C is a map

Key1	Key2
Timothy	Tim
Robert	Rob

Essentailly, in my model I have a ton of sheetB's and only 1 sheetA, so we must map the keys in sheetB to correspond to sheetA (rather than vice versa). And I'm using a formula to write many indirects. But for the sake of example,

=SUMIFS(INDIRECT(F2),INDEX(sheetC!$B$2:$B$3,MATCH(INDIRECT(E2),sheetC!A2:A3,0)),A2)

The above formula should return a value, but it instead returns an array and I'm really flabbergasted as to why.

The problem is the indirect in the index match formula, if we seperate that out and do:

=SUMIFS(INDIRECT(F2),E12#,A2)

Where E12# is the formula: =INDEX(sheetC!$B$2:$B$3,MATCH(INDIRECT(E2),sheetC!A2:A3,0))

The formula works perfectly fine. But this just confuses me more, because all I've done is broken the formula into two parts.

Does anyone understand why it might not be working as we might want?

Not sure what you are trying to achieve using INDIRECT, but give this a try, and adjust the sheet names to suit your data:

=SUMIF(Sheet2!$A$2:$A$5,INDEX(Sheet3!$A$2:$A$3,MATCH(A2,Sheet3!$B$2:$B$3,0)),Sheet2!$B$2:$B$5)

I used the default sheet names, Sheet1, Sheet2, Sheet3, but they correspond with your Sheet A, Sheet B, Sheet C.