Dynamic INDEX/MATCH

• The attached document has a "Current Portfolio" column (here column R) that can change. At the bottom of the sheet (rows 1761-1766), in that column, there are some labels I'm hoping to use for an INDEX/MATCH, VLOOKUP type formula. The formula itself will be on another sheet (on the attached, Sheet2!B8 for example.

What I'm wondering is how to use INDEX/MATCH or VLOOKUP, with the array beginning in the column where i find "Current Portfolio" (in this case, again, column R). I can't seem to wrap my head around how this should be done. One more note is that row 7 are cell references and should be dynamic (should be able to change those references around, plugging in different numbers and row 8-9 should adjust accordingly). Thanks!

Files

• In B8 try:

=INDEX(INDEX(Test!\$A1764:\$AK1764,MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)):Test!\$AK1764,MATCH(B\$7,INDEX(Test!\$A\$1:\$AK\$1,MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)):Test!\$AK\$1,0))

copied across and down to next row

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Thank you so much. This works perfectly, except I neglected to mention something. Both the width and height of the array are actually dynamic, in that not only can the "Current Portfolio" column BUT, the number of rows can change as well, such that Stock Turnover may not always be in row 1764 (similar with Minimum Trade Size). I think that might have been what I was having issues with originally. But if there's a way to make that row dynamic with another INDEX/MATCH or something to that effect, I think that might possibly work.

• Try then:

=INDEX(INDEX(Test!\$A:\$AK,MATCH(\$A8,INDEX(Test!\$A:\$AK,,MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)),0),MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)):INDEX(Test!\$AK:\$AK,MATCH(\$A8,INDEX(Test!\$A:\$AK,,MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)),0),),MATCH(B\$7,INDEX(Test!\$A\$1:\$AK\$1,MATCH("Current Portfolio",Test!\$A\$1:\$AK\$1,0)):Test!\$AK\$1,0))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• This is perfect thank you so much!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!