Dec 10 2021 06:41 AM
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?
Dec 10 2021 06:45 AM
Dec 10 2021 06:52 AM
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
Dec 10 2021 07:00 AM
Dec 10 2021 07:13 AM
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() ?
Dec 10 2021 07:20 AM - edited Dec 10 2021 07:20 AM
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.
Dec 11 2021 03:17 AM
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.
Dec 11 2021 04:45 AM
Dec 11 2021 09:22 AM
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.