Excel: Returning a cell in column A based on a MAX function in I1:I100 range

Question

In cell P3 I have the function:

=MAX(I1:I100)

This returns cell I23

In the cell next to P3 (Q3) I want to return the column A cell value corresponding to the row that the max function has determined. So in the current case, I want to return A23 (if the max function had determined I95 then I would want to return A95 and so on).

I want to do this because the I1:I100 values will change over time and so I want cell Q3 to update automatically. Is this possible?

Answer 1

So use index() with match():

=index(A1:A100,match(P3,I1:I100,0))

Or you can put the max() in the match() like so:

=index(A1:A100,match(max(I1:I100,I1:I100,0))

But it does depend on how you are laying things out.

Note, I have assumed that the range is rows 1 to 100, based on what you state. If you need to drag this formula then you need to put $ on the ranges as needed.

Answer 2

You could use this

=TEXTJOIN("; ",TRUE,IF(P3=I1:I100,ROW(I1:I100),""))

This will be usefull if you have more than 1 max value. It will return every row in text.

If you do not have Office 365, I would use Solar Mike answer. https://stackoverflow.com/a/72103448/10280921