getting a formula to return the reference of a cell

[Guest]

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

 

[Guest]

=ADDRESS(MATCH(MAX(F$1:F$23719),F$1:F$23719,0),1,4)

 

[T. Valko]

One way:

="F"&MATCH(MAX(F1:F23719),F1:F23719,0)

Biff

 

[Guest]

Great but When i cointue down and do the top ten,I get two values that are
the same (my top 10 songs , and how many records they sell , two of them
sold 53 this week ) . so I cannot use the match fuction which returns only
the match for the first occurence.
I was hoping to use the adress fuctiondirectly within the large function to
avoid the problem

 

[Guest]

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1),F$1:F$23719,0),1,4)

Copy down to as far as needed

 

[Guest]

I get a " errro in formula"

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1),F$1:F$23719,0),1,4)

Copy down to as far as needed

 

[Guest]

Here is the correct formula, the previous one was missing a bracket ")"

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1)),F$1:F$23719,0),1,4)

 

[Guest]

Still does nto work . I will try a differnt way ..

 

[T. Valko]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENETER (not just
ENTER):

="F"&MATCH(LARGE(F1:F23719-ROW(F1:F23719)/10^10,ROWS($1:1)),F1:F23719-ROW(F1:F23719)/10^10,0)

Biff