# How to Create Dynamic Filter Array



## jakeman (Dec 27, 2022)

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 (Dec 27, 2022)

How about

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


----------



## jakeman (Dec 27, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

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 (Dec 28, 2022)

You're welcome & thanks for the feedback.


----------

