# Refer to the cell directly to the left of the cell with the maximal value in a range

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.

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? Commented 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
Commented Mar 23, 2018 at 14:54