Search code examples
excelexcel-formulaexcel-match

Find maximum of row, return column name

I have four rows and six columns of random numbers between 1 and 10. The headers atop are named A through F accordingly. I want to populate a range (A1:A6) on another sheet with the maximum number for each row. That is easy with the MAX function. However, in a another range (B1:B6), I want to put the column name to which this number belongs.

An HLOOKUP() won't work because a maximum value in one row is likely not unique number across the entire sheet. I am thinking a MATCH INDEX type function, but my understanding of those functions, especially in conjunction, is poor.

A   B   C   D   E   F
1   0   2   10  9   8
9   3   7   6   9   10
10  3   0   2   1   4
9   4   7   8   6   3
Solution

  • Assuming your array is in Sheet1 and the columns are labelled, please try in another sheet, copied down to suit (to Row4 since there are only four rows of numbers in your data): 

    =INDEX(Sheet1!A$1:F$1,MATCH(MAX(Sheet1!A2:F2),Sheet1!A2:F2,0))

    This will return only the first column label from a row where the maximum for that row occurs more than once.