Forum Discussion

Copper Contributor

Apr 03, 2026

How XLOOKUP arguments apply in this case

I'm not an Excel newbie, but I have never had cause to use XLOOKUP, INDEX, or MATCH before. I'm not understanding how the xlookup arguments would apply in my case or if index/match would be better. ...

excel

office 365

IlirU

Iron Contributor

Apr 04, 2026

Hi MollyKitti,

In cell F2 apply below formula (format cell as date) - for me date is in format "dd/mm/yyyy":

=--LET(
      d, B5:AF16,
      m, A5:A16,
      y, A4,
    con, d = MAX(d),
         TOCOL(SEQUENCE(, COLUMNS(d)) / con, 3) & "-" &
INDEX(m, TOCOL(SEQUENCE(ROWS(d)) / con, 3)) & "-" & y
)

I have also placed this formula in cell AI2 because if there is more than one date with a maximum value, then the formula will spill down, showing all the dates that have this maximum value.

In cell J2 apply below formula:

=MAX(B5:AF16)

In cell T2 apply below formula:

=INDEX(A5:A16, TOCOL(SEQUENCE(ROWS(B5:AF16)) / (B5:AF16 = MAX(B5:AF16)), 3))

In cell X2 apply below formula:

=FILTER(AG5:AG16, A5:A16 = T2)

In cell AF2 apply below formula:

=SUM(AG5:AG16)

Hope this helps.

IlirU