Handling an array contining the same element multiple times

[TheDub]

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

=XMATCH(target,B2:E2,0)

returns just 1.

Harnessing BYROW to get the same information for both John and Jane

=BYROW(B2:E3,LAMBDA(myrow,XMATCH(target,myrow,0)))

returns, as expected, only
1
4

If I want to get the years in which the target is achieved, for John

=INDEX(B1:E1,,XMATCH(target,B2:E2,0))

returns only 2019, omitting 2021.
Doing the same for both

=BYROW(B2:E3,LAMBDA(myrow,INDEX(B1:E1,,XMATCH(target,myrow,0))))

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
Jane, 2022

Thanks.

[#2]

@TheDub 

=IF(B2:E3=B4,B1:E1,"")

This returns a similar output in my sheet. 

[#3]

@TheDub 

rng= A2:E3

[#4]

@TheDub 

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).

[#5]

You are absolutely right about running into the very dreaded nested array problem...

By way of illustration, I tried to take @OliverScheurich'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

=BYROW(answer,LAMBDA(myrow,FILTER(myrow,myrow<>"")))

or, since we're playing games, in reverse:

=FILTER(answer,BYROW(answer,LAMBDA(myrow,myrow<>"")))

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....)

[#6]

@TheDub 

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 ?

[#7]

A revision from my earlier formula:

=BYROW(MAP(rng,LAMBDA(x,IF(ISTEXT(x),x,IF(x=2,INDIRECT(ADDRESS(1,COLUMN(x))),"")))),LAMBDA(row,TEXTJOIN(",",TRUE,row)))

[#8]

@TheDub 

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.

[#9]

@Peter Bartholomew 

There are times reading through message threads like this when I definitely feel like Charlie Brown in this classic comic strip.

[#10]

Do we each have a rôle to play? One to shape the clouds and another to divine their significance?

I can't help but feel that there is a change of mindset required to exploit the new functionality to best effect. Before, I would have been 'authoring a formula' (albeit using a highly idiosyncratic syntax), now it is more a case of writing a short program module. However, I do not like the need to resort to 'thunks', other than as a last resort to address 2D arrays of row vectors for example. A simple column of row vectors should just display on the worksheet without trickery.