10

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?

13

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.

  • Thanks so far. But the Match function seems to have an error. "Error parsing function". – tzippy Apr 9 '14 at 14:32
  • Okay, I had to use semicolons because I'm in a different locale. Works now! – tzippy Apr 9 '14 at 14:36
  • OK, good :) The ; vs , is a locale issue. – Vidar S. Ramdal Apr 9 '14 at 14:37
  • What if there are 2 rows matching the max value? – Gerbus May 14 '16 at 15:28
  • @Gerbus I guess you mean "What if there are 2 columns matching the max value?". MATCH only returns a single value, so the result will be the heading of the first column with the max value. So if A=3, B=2, C=3, the result will be A. – Vidar S. Ramdal May 18 '16 at 7:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.