Return Location of k-th largest in data set

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Is there a way to return the location of a cell that is
the k-th largest in a range? For example, in cells A1
through A4 I have the values, 1, 2, 3, and 4. I'd like to
return the location of the whatever largest cell. The
LARGE function only returns the value, not the location.
Thanks!
 
Hi Rob
to return the row number of this cell you may try
=MATCH(LARGE(A1:A10,k),A1:A10,0)

Note: This works if your range starts in row 1. If the range starts in
a different row you have to change the above formula accordingly
 
Rob,

The following formula will return the 1-based index into C3:C10
of the second largest value in that range.
=MATCH(LARGE(C3:C10,2),C3:C10,0)

Change the '2' in the LARGE function to whatever value you are
interested in.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
=ADDRESS(MATCH(LARGE(A1:A4,K),A1:A4,0),COLUMN(A1:A4),4)

where K = k-th largest. In general, for any vertical range
(rng), regardless of the range location:

=ADDRESS(MATCH(LARGE(rng,K),rng,0)+ROW(rng)-1,COLUMN
(rng),4)

HTH
Jason
Atlant, GA
 
Back
Top