Handling an array contining the same element multiple times

Question

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 https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 barely mentions that:

"The XLOOKUP function searches ... an array, and then returns the item corresponding to the first match it finds."

The https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312 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.

mathetes · Answer

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

oliverscheurich · Answer

TheDub&nbsp;=IF(B2:E3=B4,B1:E1,"")This returns a similar output in my sheet.&nbsp;

patrick2788 · Answer

TheDub&nbsp;rng= A2:E3&nbsp;&nbsp;

peterbartholomew1 · Answer

TheDub&nbsp;The problem you describe is remarkably difficult because you hit the dreaded nested arrays problem.&nbsp; The first stage is reasonable OK.&nbsp; Given a single record,&nbsp;&nbsp;&nbsp;= FILTER(year, record=target)&nbsp;&nbsp;identifies the years the target is achieved.&nbsp; The catch is that as soon as you try to use BYROW to create a 2D output array, Excel throws an error.&nbsp; One way of evading the problem is to turn the row result into a single concatenated value.&nbsp; Then the BYROW step works.&nbsp;&nbsp;= BYROW(table, 
    LAMBDA(record,
      LET(
        yrs, FILTER(year, record=target),
        TEXTJOIN(", ",, yrs)
      )
    )
  )&nbsp;&nbsp;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).

thedub · Answer

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

Forum Discussion

Handling an array contining the same element multiple times

9 Replies

Resources