Forum Discussion
LET and Nested Dynamic Array FILTER Function
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?
8 Replies
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.
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
- 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?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() ?
- 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?
