Return row label for large value in excel

Question

I have a problem here with this command in excel

=INDEX(A2:A41;MATCH(LARGE(M2:M41;1);M2:M41;0))

Data:

A          M 
name1      0,55
name2      2.01
name3      10.00
name4      2.01
name5      5.00

Formulas:

=INDEX(A2:A41;MATCH(LARGE(M2:M41;1);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;2);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;3);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;4);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;5);M2:M41;0))

These commands will return this:

TOP 5 Values:
name5 
name3
**name2**
**name2**
name1

The problem is that if two values are the same p.ex. name2 = name4 = 2.01, "name2" appears two times, "name4" none.

I am trying to make it return this:

name5 
name3
**name2**
**name4**
name1

Or this:

name5 
name3
**name4**
**name2**
name1

The order does not matter

EDIT 1
Same problem.. This formula:

=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(1:1)))*1E+99;;);COUNTIF($M$2:$M2;$M2)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(2:2)))*1E+99;;);COUNTIF($M$3:$M3;$M3)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(3:3)))*1E+99;;);COUNTIF($M$4:$M4;$M4)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(4:4)))*1E+99;;);COUNTIF($M$5:$M5;$M5)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(5:5)))*1E+99;;);COUNTIF($M$6:$M6;$M6)))

Returns the same result with this:

=INDEX(A2:A41;MATCH(LARGE(M2:M41;1);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;2);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;3);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;4);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;5);M2:M41;0))

In case of two same values on column M, "name2" appears two times, the another dont appear at all...

EDIT 2
I just want the TOP 5 values of the column M, and return their rows like this:

TOP 5

name5 
name3
name2
name4
name1

isn't there any simple command that could rearange the column M from highest value to lowest value? And return row names, not numbers.

Same problem.. This formula:

=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(1:1)))*1E+99;;);COUNTIF($M$2:$M2;$M2)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(2:2)))*1E+99;;);COUNTIF($M$3:$M3;$M3)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(3:3)))*1E+99;;);COUNTIF($M$4:$M4;$M4)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(4:4)))*1E+99;;);COUNTIF($M$5:$M5;$M5)))
=INDEX($A$2:$A$41;SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41;ROW(5:5)))*1E+99;;);COUNTIF($M$6:$M6;$M6)))

Returns the same result with this:

=INDEX(A2:A41;MATCH(LARGE(M2:M41;1);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;2);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;3);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;4);M2:M41;0))
=INDEX(A2:A41;MATCH(LARGE(M2:M41;5);M2:M41;0))

In case of two same values on column M, "name2" appears two times, the another dont appear at all... Have i done something wrong?

Unknown Unknown · Accepted Answer · 2014-11-17T00:59:01

There may be an array formula offered somewhere down the pipe but here is a standard formula solution.

second LARGE value

The formula in O2 is,

=INDEX($A$2:$A$41,SMALL(INDEX(ROW($1:$40)+($M$2:$M$41<>LARGE($M$2:$M$41,ROW(1:1)))*1E+99,,),COUNTIF($M$2:$M2,$M2)))

Fill down as necessary.

Return row label for large value in excel

1 Answers