How to Create Dynamic Filter Array

[jakeman]

I built a filter for records using the FILTER function and so far it works great. The problem is that I have a need to make one aspect of my filter dynamic but I am not able to do it.

Here is my current formula.

=FILTER(INDEX(table1,SEQUENCE(ROWS(table1)),{1,6,17}),table1[LC1▲]=1)

The array constant part works fine but I realized that I have a need to change which columns I want to show in my formula and my preference would be to change the array constant to a dynamic array instead. I've tried different combinations but I cannot get anything to work. So instead of {1,6,17}, I'd prefer to use a range like B3:B5, which contains 3 different numbers that can change depending on selections made elsewhere.

Can anyone lend any ideas?

Thank you!

 

[Fluff]

How about

=FILTER(CHOOSECOLS(Table1,B3:B5),Table1[LC1▲]=1)

 

[jakeman]

Thank you, Fluff.

For some reason, my version of Office 365 does not have this function available. I'm on Office 365 Enterprise.

Any thoughts on a workaround approach?

 

[Fluff]

Ok, you'll probably get the new functions in January, but how about

=FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),TRANSPOSE(B3:B5)),Table1[LC1▲]=1)

 

[jakeman]

This works beautifully. I have not seen the TRANSPOSE function in use before so that was clearly the missing piece for my function.

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.