Index formula with dynamic array reference-CodePudding

I have a table that is referencing a pivot table on the same sheet. I added a form control scroll bar to be able to flip through the data without changing the view.

The headers of that table are linked to the headers of the pivot table. I am trying to write the =INDEX(L3:L$17,$D$6) formula so that the column info is adjusted automatically.

I managed to use the columnletter to convert the match of the header to the correct letter but don't know how to adjust the Index formula to use that info.

The goal is to change the index column array reference whenever I change the header dependency to another column.

change to Header from City to Street, should change the Index formula to use column M instead of L

CodePudding user response：

The Match function will search the header word in the pivot table. So you can change the words in the header and get the results for it without changing the formula.

   Option 1  E8=    =INDEX($L$3:$N$26;$F$3 ROWS($E$8:E8)-1;MATCH(Q$6;$L$2:$N$2;0))

Option 2  Q7=INDEX($L3:$N$26;$F$3;MATCH(Q$6;$L$2:$N$2;0))