getting a formula to return the reference of a cell

C

CB

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

CB

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
 
T

Teethless mama

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

Copy down to as far as needed
 
T

Teethless mama

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)
 
T

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
 
Top