I've got a spreadsheet with about 5,000 rows and roughly 50 columns. The columns are alternating between terms and numeric values associated with the terms (example: baseball / 1000 / football / 500 / soccer / 600 / hockey / 750 / track / 100).

To the right of all of the columns, I'd like to add a summary column that selects the highest value and returns that value's associated term.

I've used MAXA for finding the highest value (=maxa(A1:J1)) and I believe INDIRECT is a good way to return the word(s) in the cell directly to the left of the highest value cell (=indirect("r[0]c[-1]",false)).

What I can't figure out is how to combine MAXA and INDIRECT or nest them so they'll work together.

Any advice?


Using index and match is another way to work with arrays using relative-to-range references:

=index(A1:J1, match(maxa(A1:J1), A1:J1, 0) - 1)

Here, match finds the position of the maximum number in the range A1:J1. Subtracting 1 gives the index of the cell to the left of it. And then index returns the content of that cell.

References: index, match, offset.

  • Thanks! Followup question: I'm not surprised to see that you did this without INDIRECT, since I don't see a lot of people using it. Is it very infrequently used? – gesher Mar 23 '18 at 14:51
  • You can search this site for INDIRECT to see examples of it being used. For example, to refer to a static row regardless of row insertions/deletions; also, to refer to another sheet within a conditional formatting formula. – user135384 Mar 23 '18 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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