Apr 27 2022 09:29 AM
Apr 27 2022 09:29 AM
One of the things (among quite a few) I find frustrating with the new-generation functions in Excel is their occasional limited scope. Two examples are XLOOKUP() and XMATCH: it took me a while to understand that they only return one result, even if more than one is present.
The XLOOKUP support page barely mentions that:
"The XLOOKUP function searches ... an array, and then returns the item corresponding to the first match it finds."
The XMATCH support page doesn't even bother to mention it:
"The XMATCH function searches for a specified item in an array..., and then returns the item's relative position."
I would have emphasized that limitation with big, bold red letters, but that's just me...
So given that fact and this simple worksheet, how do I accomplish the following:
First and simplest: Taking the first entry ("John"), I would like to get the index positions of his row where the target is achieved. The answer in his case is 1 and 3, but, true to form
returns just 1.
Harnessing BYROW to get the same information for both John and Jane
returns, as expected, only
If I want to get the years in which the target is achieved, for John
returns only 2019, omitting 2021.
Doing the same for both
returns the wrong result for John, but the right result for Jane (since she only met the target once).
So how do I go about getting an output by person and year, along these lines:
John, 2019, 2021
Apr 27 2022 12:49 PM - edited Apr 27 2022 01:08 PM
The problem you describe is remarkably difficult because you hit the dreaded nested arrays problem. The first stage is reasonable OK. Given a single record,
= FILTER(year, record=target)
identifies the years the target is achieved. The catch is that as soon as you try to use BYROW to create a 2D output array, Excel throws an error. One way of evading the problem is to turn the row result into a single concatenated value. Then the BYROW step works.
= BYROW(table, LAMBDA(record, LET( yrs, FILTER(year, record=target), TEXTJOIN(", ",, yrs) ) ) )
To get an array of arrays requires MAKEARRAY to expanding an inner Lambda function (probably a thunk - that is a Lambda function that takes a null parameter string).
Apr 27 2022 01:44 PM
You are absolutely right about running into the very dreaded nested array problem...
By way of illustration, I tried to take @Quadruple_Pawn's answer (and the same goes for @Patrick2788's answer) and filter out the spaces from the output. That called for combining FILTER() and BYROW() this way
or, since we're playing games, in reverse:
Sure enough, a #CALC error popped up with the message "Nested arrays are not supported"!
I'm going to turn now to playing with your answer and see what I get, but - off the bat - I have to say that the idea adults (presumably) like us have to resort to workarounds like "turn the row result into a single concatenated value" to get to the promised land makes me shake my head (or SMH, as they say nowadays....)
Apr 27 2022 03:16 PM
I feel slightly embarrassed to offer this solution to your problem. It shouldn't be this complicated!!!
= LET( achievedϑ, BYROW(table, LAMBDA(record, Thunkλ(SORTBY(IF(record=target, year, ""), record<>target)))), MAKEARRAY(2,2, Expandλ(achievedϑ)) )
where forming and expanding the Thunk is achieved by
Thunkλ = LAMBDA(x,LAMBDA(x)); Expandλ = LAMBDA(achievedϑ,LAMBDA(r,c, INDEX(INDEX(achievedϑ,r,1)(),c)))
What it does is to replace the 1D array of 1D arrays by a 2D array of scalars. The latter is legal whereas the former is a sin beyond comprehension! @JoeMcDaid ?
Apr 28 2022 05:33 AM
Apr 29 2022 01:22 AM - edited Apr 29 2022 01:24 AM
In my most recent post on this thread, I had got to the point of returning the result for all names as a single array formula through the use of thunks. The obvious shortcoming was that the number of rows and columns output was set to 2x2 and required the user to modify the formula. A version of the formula that extracts the size of the output from the thunk using
... n, ROWS(achievedϑ), m, MAX(MAP(achievedϑ, Countλ)), MAKEARRAY(n,m, Expandλ(achievedϑ)) where Countλ refers to = LAMBDA(x, COUNT(IF(TYPE(x)=128,x(),x)))
If Countλ is passed an array, it performs the count; if it is passed a thunk, it expands the thunk before counting.
Apr 29 2022 06:47 AM
There are times reading through message threads like this when I definitely feel like Charlie Brown in this classic comic strip.
Apr 29 2022 09:17 AM