Combine XMATCH with MAP, BYROW, SCAN or any other LAMBDA helper.

[Geir Hogstad]

Hi,

The goal here is to find the column in area C5:I16 for each row, based on the value in E19:E30.

My idea was to use some of the new LAMBDA helpers, combined with XMATCH. Use that result and wind the right year from C4:I4

Here with a start using MAP, but MAP only displays/use the first column in the selcted area

Best Regards

- Geir

[#2]

@Geir Hogstad

Not sure I understood what do you mean, perhaps

=BYROW(
    SEQUENCE(ROWS(B5:B16)),
    LAMBDA(k,
        XLOOKUP(
            MIN(CHOOSEROWS(C5:I16, k)),
            CHOOSEROWS(C5:I16, k),
            C4:I4
        )
    )
)

[#3 · Accepted solution]

@Geir Hogstad

Is this the sort of thing you are looking for?

If so, possible formulas might include

= BYROW(data, LAMBDA(row, 
     XLOOKUP(MIN(row), row, År)
  ))

or

= MAP(Lavest#, SEQUENCE(12),
      LAMBDA(lav,m, XLOOKUP(lav, CHOOSEROWS(data,m), År))
  )

[#4]

Perfect, thank you That was exactly the result I was looking for.

Best Regards

- Geir

@Peter Bartholomew

[#5]

@Geir Hogstad

Thank you for sharing this workbook. Lots of options with these functions:

=BYROW(C5:I16,LAMBDA(row,TAKE(DROP(B4:I4,,XMATCH(0,row,1)),,1)))