CODEWITHSUNDEEP
excelgoogle-sheetsspreadsheet
Denis Hoctor
Denis Hoctor

Reputation: 2607

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

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.

Upvotes: 6

Views: 7980

Answers (4)

khangnpq
khangnpq

Reputation: 1

As of now, there is a QUERY formula that can help with this situation

=ARRAYFORMULA(query({ROW(A2:A), A2:A},
"SELECT MAX(Col1)
WHERE Col2 IS NOT NULL
ORDER BY Col2 DESC"))

ROW(A2:A) represents the row number of each row which you can use the result with ADDRESS to create a reference to a cell.

Upvotes: 0

Julien Marrec
Julien Marrec

Reputation: 11895

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

Upvotes: 2

cup
cup

Reputation: 8247

=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.

Upvotes: 4

Michael Moulsdale
Michael Moulsdale

Reputation: 1488

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

Upvotes: 1

Related Questions