# Excel Formulas (Indirect(index(match)))

Copper Contributor

# Excel Formulas (Indirect(index(match)))

Hello,

I have two worksheets, TestFund (First below) and "Aggregate" second image below.  I am trying to transpose the info from the TestFund worksheet in the "Aggregate" worksheet and i am currently doing so with a sumif formula.   My problem is i want to use a formula that can pull all the information in without my having to update the last argument in the sumif formula.  as an example in the aggregate tab my formula pulls row from TestFund!\$7:\$7 the TestFund tab.  when i paste the formula over into column C i have to go into the sumif formula and change the row reference to TestFund!\$8:\$8.  I know there is a way to do this with using indirect and index(match) but i do not know how to put the formula together.

Any help would be greatly appreciated.

I

3 Replies

# Re: Excel Formulas (Indirect(index(match)))

I'm not sure if this is just a learning thing or maybe just an example as what I see could easily done with 1 formula. The whole range on aggregate A16:E20 could be this formula in A16:
=TRANSPOSE(TestFund!A6:E10)
I really don't see the need for SUMIF but lets assume a very generic case and using just index and match:
=index(\$B\$7:\$E\$10, match(\$A17,\$A\$7:\$A\$10,0), match(B\$16, \$B\$6:\$E\$6, 0) )

# Re: Excel Formulas (Indirect(index(match)))

@mtarler Thank you for your response.  This is a closed and simplified example but imagine I have "TestFund(n)" worksheets,  all in the same format as "TestFund".  In the "Aggregate" worksheet i will have TestFund(N) columns * testFund(n):Investors.  Im trying to understand how i can drag a formula across the aggregate worksheet such that it references "TestFund(n)" tab, find the row the investor is sitting on, matches the income type, and pulls in that number.