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

protected by Community Oct 8 at 18:31

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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