Updating a Unique/Filter formula to find the correct column

[erniepoe]

Hello all,

I currently have the simple Unique formula as follows:

=UNIQUE(FILTER($E$12:$E$10000,($AI$12:$AI$10000>0))).

Column AI is the column that corresponds to the date, so was hoping to build one dynamically that would automatically find the correct column based on the date, rather than have to manually change the formula each month to AJ, AK, etc...

The date can be found in cell A1, and corresponds to row 10, and I came up with the following, which is not working.

=UNIQUE(FILTER($E$12:$E$10000,INDEX(F12:AP10000,MATCH(A1,F10:AP10,0))>0))

Any help in making this a less manual activity each month would be most appreciated. Thank you so much.

 

[hagia_sofia]

How about:

=UNIQUE(FILTER($E$12:$E$10000,(CHOOSECOLS($F$12:$AP$10000,XMATCH(A1,F10:AP10))>0)))

 

[erniepoe]

that worked brilliantly, thank you so much hagia_sofia!

 

[hagia_sofia]

that worked brilliantly, thank you so much hagia_sofia!

Thanks for the feedback!