Find cell with highest value of a cell range

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

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.

Attribution
Source : Link , Question Author : tzippy , Answer Author : Vidar S. Ramdal

Leave a Comment