How do I return the cell address of the largest of a set of values

[Mr. Snrub]

I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the largest value.

=LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
address where that largest value is located?

 

[papou]

Hello
Try this array formula (validate with Ctrl + Shift + Enter):
=ADDRESS(MIN(IF(B3:Z51=LARGE(B3:Z51,1),ROW(B3:Z51))),MIN(IF(B3:Z51=LARGE(B3:Z51,1),COLUMN(B3:Z51))))

HTH
Cordially
Pascal

 

[Aladin Akyurek]

I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the largest value.

=LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell
address where that largest value is located?

Be aware of the fact that there can be multiple instances of the largest
value. See:

http://tinyurl.com/dph4d

 

[Ragdyer]

Just another way:

=ADDRESS(MAX((B3:Z51=MAX(B3:Z51))*ROW(B3:Z51)),MAX((B3:Z51=MAX(B3:Z51))*COLU
MN(B3:Z51)))

 

[papou]

Hi
I would also recommend to Mr Snrub the MAX function because of Aladin's
judicious remark.

Cordially
Pascal

 

[Mr. Snrub]

Yes, when there is the exact same value in two different cells, I want to be
able to find both cell addresses.

For example, LARGE(B3:Z51, 3) will find the third-largest value, and
LARGE(B3:Z51, 4) will find the fourth-largest value. If those two values are
the same, how do I find their corresponding addresses? papau's formula don't
seem to work in this case (it repeats the first cell address found when there
is a "tie"), and (as far as I know) MAX only finds the largest value, not the
second-, third- or fourth-largest.

Thanks for all your help, by the way!

Mr. Snrub

 

[Aladin Akyurek]

Yes, when there is the exact same value in two different cells, I want to be
able to find both cell addresses.

For example, LARGE(B3:Z51, 3) will find the third-largest value, and
LARGE(B3:Z51, 4) will find the fourth-largest value. If those two values are
the same, how do I find their corresponding addresses? papau's formula don't
seem to work in this case (it repeats the first cell address found when there
is a "tie"), and (as far as I know) MAX only finds the largest value, not the
second-, third- or fourth-largest.

Thanks for all your help, by the way!

Mr. Snrub

:

The link I quoted describes a formula system of mine that is capable of
returning cell addresses of all max/largest value instances.

 

[Mr. Snrub]

I tried doing that little example, but I only got $A$2, not $A$2 and $A$6.

Mr. Snrub

 

[Martin P]

People talk of helper columns, but I have a suggestion with a few helper
worksheets. For convenience the worksheets have been given names. A few
ranges have also been given names:
blok list!$B$3:$Z$51
columnnumbers columnnumbers!$B$3:$Z$51
rownumbers rownumbers!$B$3:$Z$51
unique unique!$B$3:$Z$51
uniquerank 'unique ranks'!$B$3:$Z$51
List
Contains the values in cells B3 to Z51. A3 to A51 are numbered from 1
upwards, and also B2 to Z2.
Ranks
Cell B3: =RANK(list!B3,blok) (copied to B3:Z51)
Unique
Cell B3: =RANK(list!B3,blok) (copied to B3:Z51)
Unique ranks
Cell B3: =RANK(unique!B3,unique) (copied to B3:Z51
Rownumbers
Cell B3: =ROW(A3) (copied to B3:Z51)
Columnnumbers
Cell B3: =COLUMN(A3)
Sheet8
From B1 to F1 the column headings are Row, Column, Value, Unique value, Rank
In column A from cell A2 the cells are numbered from 1 upwards.
Cell B2: =SUMPRODUCT(rownumbers,--(uniquerank=$A2))
Cell C2: =SUMPRODUCT(columnnumbers,--(uniquerank=$A2))
Cell D2: =SUMPRODUCT(blok,--(uniquerank=$A2))
Cell E2: =SUMPRODUCT(unique,--(uniquerank=A2))
Cell F2: =SUMPRODUCT(ranks!$B$3:$Z$51,--(uniquerank=$A2))
Copy the cells down as far as is necessary.