2

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • 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, 2018 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, 2018 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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