Find Cell with Highest Value in Google Sheets

google sheets

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?

Best Answer

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.

Related Topic