I have some data in an Excel sheet. I want to find the maximum value in each row and return the corresponding column name. Which Excel functions should I use for it?

+3 votes

Best answer

You need to use 3 Excel functions, *INDEX, MATCH*, and *MAX*, to get the column name of the largest value in a row.

Let's say you have data in columns A to D. Your column names are in row 1, and data are in rows 2...n.

To find the column name with the max value in row 2, you will write

"**=INDEX($A$1:$D$1, MATCH(MAX(A2:D2), A2:D2,0))**"

in an empty cell in row 2 and hit ENTER. For rows 3...n, you can copy-paste the above combination of functions. Excel will change the row number for you.

Here is a screenshot of an example: