Find cell with highest value of a cell range

Question

I have a row of percent values. In the last of the row (Column E) I want the text to appear, that is in the very first row above the values:

A       B      C       D         E
-----------------------------------
foo    bar    test    foobar
-----------------------------------
10%    13%     3%      1%      bar
-----------------------------------
1%    13%     30%      1%      test
-----------------------------------
9%     3%      3%      11%     foobar

What's the formula to put in the cells in column E?

Answer 1

This formula should do the trick for the first row:

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

Drag the formula across to the other rows, and the A2:D2 range should automatically be adjusted to A3:D3, A4:D4 etc.

Explanation:

• The MAX function returns the highest value of the range A2:D2. For row 2, this would be 13%.
• Next, the MATCH function returns the index of that value within the given range. This would be 2 (column B).
• Finally, INDEX returns the value of a cell in the A1:D1 range, row 1, with the index returned from MATCH as column index.

Feel free to copy the example spreadsheet I've set up. Also, refer to the Google Spreadsheets function list.