How can I find the spreadsheet cell reference of MAX() in a range?

Question

With a column range of A1:A20 I have found the largest number with =MAX(A1:A20). How can I find the reference (e.g. A5) of the MAX() result?

I'm specifically using Google Spreadsheets but hope this is simple enough to be standard across excel and Google Spreadsheets.

Answer 1

=MATCH(MAX(A1:A120),A1:A120,0)

The problem is if you get more than one match: it will only give you the first one.

Answer 2

Either :

=ADDRESS(MATCH(MAX($A:$A),$A:$A,0),1)

if you know it's going to be in column A (1)

Or:

=CELL("address",INDEX($A:$A,MATCH(MAX($A:$A),$A:$A,0)))

And yes, it only gives you the first match

Answer 3

You can use something on the lines of the following

=MATCH(MAX(E7:E9),E7:E9)

This will give you the offset from the formula to the biggest number. If you use this in conjunction with ADDRESS you could get the actual cell. However, using offset should probably help you get there if you want to do something on that cell, or another number in the same row as that cell