Forum Discussion

MollyKitti's avatar
MollyKitti
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.  I have an array with months as my row headings and dates as my column headings.  The array itself is filled with number of pages read each day.  I'm trying to have a cell indicating on which date I did the maximum amount of reading.  So I want to start with the max value of my array and return the month (row) and date (column) heading using xlookup.  Whether those are in one cell or two, doesn't so much matter - it's a hobby tracker, but no matter how I try and call this, I end up with a value error

 

So for example, I would want this to return January 3rd.  Every example I'm finding seems to show how to input Jan 3rd and return the value 758.  What am I missing to look this up the other way around?  Should I be using index or match instead?

excel
office 365

2 Replies

  • IlirU's avatar
    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

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Apr 03, 2026

    so you are correct this is backwards from the typical usage.  However here is one option:

    =XLOOKUP(J1,BYROW(B4:AF7,MAX),A4:A7)&" "&XLOOKUP(J1,BYCOL(B4:AF7,MAX),B3:AF3)

    or by naming the ranges it looks like this:

    =XLOOKUP(maxCount,BYROW(data,MAX),months)&" "&XLOOKUP(maxCount,BYCOL(data,MAX),days)

    either way I used BYROW(...MAX) and BYCOL(...MAX) to find the max value in each row/column so I could then use the xlookup accordingly (i.e. 1 row to output from a row or 1 column to output from a column)

    there is a problem with this solution in that if you have a tie it will take the first column (day) it finds and the first row (month) it finds which may not be one of the real max day results (e.g. if April 1 also has 758 then the output will be Jan 1 instead of Jan 3). So a solution that will correctly output all the max days could be:

    =TEXTJOIN(", ",1,MAKEARRAY(12,31,LAMBDA(m,d,IF(INDEX(data,m,d)=maxCount,INDEX(months,m)&" "&d,""))))

    so in this case I make an array that is 12 rows by 31 days (size of the data) and look at the corresponding day and if it matches the maxCount then return the Month Day and then join all those values.

    alternatively you can also do it this way:

    =TEXTJOIN(", ",1,TRIM(IF(data=maxCount,months,"")&" "&IF(data=maxCount,days,"")))

    in this case I just act on the whole data table to apply the month and then again to apply the days and then textjoin again