Sum of multiple index/match results

[kawaik]

Hi, I am trying to compile financial statements and am trying to put together a formula to pull account numbers into their proper reporting lines and years. Currently, I am just adding up multiple index/match formulas with a "+" between them. Is there a way to use a single index/match as an array to sum up all of the results from various lookup values? Here is an example of one of the formulas:

=(IFERROR(INDEX(Sheet2!$A$1:$AG$500,MATCH($A5, Sheet2!$C$1:$C$509,0),MATCH(C$1, Sheet2!$A$1:$AG$1,0)),0))+(IFERROR(INDEX(Sheet2!$A$1:$AG$500,MATCH($B5, Sheet2!$C$1:$C$509,0),MATCH(C$1, Sheet2!$A$1:$AG$1,0)),0))+(IFERROR(INDEX(Sheet2!$A$1:$AG$500,MATCH($A5, Sheet2!$C$1:$C$509,0),MATCH(B$1, Sheet2!$A$1:$AG$1,0)),0))+(IFERROR(INDEX(Sheet2!$A$1:$AG$500,MATCH($B5, Sheet2!$C$1:$C$509,0),MATCH(B$1, Sheet2!$A$1:$AG$1,0)),0))

As you can see, this is rather unwieldy and that is one of the shorter examples. I have also attached a sample of the data. Thanks in advance for any help. Sample data.xlsx

[ncmay]

Try the formula below in cell L4, then copy and paste to see if this does what you want.

Please Login or Register  to view this content.

[quekbc]

Hi kawaik

You could use an array formula such as this

Please Login or Register  to view this content.

It can be further simplified if the year periods between the data and the financials are the same (i.e. 2011 to 2015 for both data and financials)

Please Login or Register  to view this content.

I've put these formulae into the attached file.

NOTE: As this is an array formula, remember to press and hold CTRL and SHIFT before hitting ENTER. (i.e. type formula, hold CTRL SHIFT, hit ENTER)

[kawaik]

The array formula here does the trick. Unfortunately I do have to match the years because of the formatting on the financials themselves. Thanks for the help quekbc and ncmay.

[kawaik]

Hi kawaik

You could use an array formula such as this

Please Login or Register  to view this content.

It can be further simplified if the year periods between the data and the financials are the same (i.e. 2011 to 2015 for both data and financials)

Please Login or Register  to view this content.

I've put these formulae into the attached file.

NOTE: As this is an array formula, remember to press and hold CTRL and SHIFT before hitting ENTER. (i.e. type formula, hold CTRL SHIFT, hit ENTER)

Just out of curiosity, what causes the index(match()) in the first formula to return a cell reference instead of a value?

[quekbc]

Just out of curiosity, what causes the index(match()) in the first formula to return a cell reference instead of a value?

I don't know exactly apart from that's how it's programmed into Excel. There are a few functions in Excel that returns the "reference" rather than the value. This info can be found in Excel Help (F1). For example, the first sentence for INDEX function is "Returns a value or the reference to a value from within a table or range."

As such, you can do references like A1:INDEX(B1:Z1,10)

A few other functions that returns the cell reference are INDIRECT and OFFSET.