LET and Nested Dynamic Array FILTER Function

[Steve1330]

I'm looking for a way to reference a FILTERED dynamic array column, nested inside the LET functions, e.g.:

LET(A,FILTER(A:F,(A:A="X")*(B:B<=DATEVALUE("12/31/2021"))), A_____)

How would I reference the first column of the A LET range variable at the end of the LET function, or is this not possible?

[#2]

Alternatively, is there a way to reference a FILTERED dynamic array column, nested inside another FILTER function, e.g.:
FILTER(FILTER(A:F,(A:A="X")*(B:B<=DATEVALUE("12/31/2021"))), A_____)
and not just return the columns, but also use criteria on them, or is this also not possible?

[#3]

@Steve1330 

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

[#4]

Sergei:
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?

[#5]

@Steve1330 

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

[#6]

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.

[#7]

@Steve1330 

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.

[#8]

Sergei, good advice. I tried adding helper columns to the source data to improve performance as there's a noticeable delay (1-2 seconds) after the User flips the date ranges that are used by the formula for the result. But since performance is really not a major concern, I'll stick with the long formula that works perfectly across multiple data sets. Thanks again.

[#9]

@Steve1330 

If you filter an array, it is possible to select columns from the result using INDEX

= LET(
    A,  FILTER(array,(colA="X")*(colB<=DATEVALUE("31/12/2021"))),
    k,  SEQUENCE(ROWS(A)),
    A₀, INDEX(A,k,{2,3,5}),
    A₀)

The resulting object is an array and not a range reference so you cannot apply COUNTIFS to it, but any array calculation is possible.  The local variable, here A₀, can be used for on-going calculation within the LET function, or the LET itself may be nested within further formulae. 

If you require the result to be in the form of a range reference, you could place the formula on a distant sheet and address the spilt helper range.