Index formula with dynamic array reference

Question

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

WeAreOne · Accepted Answer · 2022-08-26 15:45:56Z

1

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))

edited Aug 26, 2022 at 15:45

answered Aug 26, 2022 at 7:15

WeAreOne

1,3731 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

H BG Over a year ago

Thanks. Apologies for lack of clarity. I want the index formula to change according to the column referred to in the header. So if the header (city) refers to column L, the index array is to capture L3:L17. If there would be a change in header dependent to e.g. column M, the formula is to update to M3:M17 without the user having to manually change the Index formula.

H BG Over a year ago

i added another picture to maybe better explain. If I would change the header from City to Street, the index formulas below, should automatically change from column L to M based on the columnletter match result.

WeAreOne Over a year ago

@hbg i updated the answer. I think it is that you want

Collectives™ on Stack Overflow

Index formula with dynamic array reference

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related