How to find the column header and row header of the max value in a table-CodePudding

I am trying to return the column and row headers (goals) for the most likely event in this table, for example. According to the table, the most likely score at the end of the Calgary Flames and Florida Panthers game is 4-2 with Florida Panthers taking home the W.

For a little more detail... I am trying to find the max value in the table and then return the corresponding column header in one cell and the corresponding row header in another cell. I have seen quite a bit of examples online, but I haven't been able to get any of the formulas to work so far so I thought I'd try here. Thanks!

CodePudding user response：

You can try the following formula in cell M2:

=LET(rng, B2:K11, n, ROWS(rng), m, COLUMNS(rng), lookup, MAX(rng),
  rows, MAKEARRAY(n, m, LAMBDA(r,c,r)),
  cols, MAKEARRAY(n, m, LAMBDA(r,c,c)),
  found, MAP(rows, cols, rng, LAMBDA(r,c,m, IF(m=lookup, r&","&c,""))),
  TEXTSPLIT(TEXTJOIN(";",, found),",", ";")
)

Here is the output:

This solution takes into account that could be more than one cell with the maximum value, if that is the case it returns more than one row with row and column information, like in the above example.

Note: The result will be numbers as text. If you want numbers, then multiply the TEXTSPLIT output by 1.