Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Steel Contributor

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

find year.png

4 Replies

@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
        )
    )
)
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad

Is this the sort of thing you are looking for?

image.png

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

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

Best Regards

- Geir

@Peter Bartholomew

@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)))
1 best response

Accepted Solutions
best response confirmed by Geir Hogstad (Steel Contributor)
Solution

@Geir Hogstad

Is this the sort of thing you are looking for?

image.png

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

View solution in original post