Forum Discussion
LET and Nested Dynamic Array FILTER Function
That could be
LET(a, FILTER(A:F,(A:A="X")*(B:B<=DATEVALUE("12/31/2021"))), INDEX(a,,1) )but it returns few "X" since you filter first column on it
Thanks for your very quick reply, but rather than just return that column, can I use the INDEX function to filter one or more columns of the dynamic array by criteria?
Directly not. INDEX( array, rows, columns) returns part of the array for specified rows and columns, nothing more. Within INDEX you may filter array, or calculate which rows and columns to return using another functions. But why if you have FILTER() ?
I'm trying to find a way to filter a Dynamic Array FILTER based on multiple rows that go together. I have a very long formula that works, where I use COUNTIFS in a LET function to help me out, but I'm trying to find a way to condense that very long formula and get the same result. Thanks again for your help.
That's better to discuss on concrete sample. Long, but well structured formula, could be more preferable from maintenance point of view. The only compromise if that affects performance. Depends on how large is your data.
In general, if performance is not critical, it's always better to use formulae in a way you most familiar with. Even if they are not optimal from other Pro:s point of view. With that you save lot of time on maintenance. If approach is totally new for you, try this and that and stop on most comfortable one. Power of Excel is what you may achieve the same result by few ways, which one to select depends on your data and your skills. All work.