+1 vote
in General IT Tips by (73.8k points)
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?

1 Answer

+3 votes
by (348k points)
edited by
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:

excel column name with max value