Return top performer, second top, third top, etc

[Simon4s]

In A1 i have:
=INDEX(D2:D6,MATCH(LARGE(E2:E6,C2,E2:E6,FALSE))


This formula returns the top most valuable customer (dog). But there is a tie for second place (cat and amy) but it returns Amy Twice. How do i fix this? Ty

---

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Waffle[/TD]
[TD]90[/TD]
[TD]1[/TD]
[TD]Dog[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD]Cat[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Amy[/TD]
[TD]50[/TD]
[TD]3[/TD]
[TD]Amy[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]30[/TD]
[TD]4[/TD]
[TD]Stick[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Stick[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Waffle[/TD]
[TD]90[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
Your formula doesn't function ?

Here is a macro that will sort as you desire :

Option Explicit


Sub srtCol()


Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
End Sub

 

[bosco_yip]

1] In A2, copied down :

=LOOKUP(2,1/(LARGE(E$2:E$6+1/ROW($1:$5),ROWS($1:1))=E$2:E$6+1/ROW($1:$5)),D$2:D$6)

2] In B2, copied down :

=LARGE(E$2:E$6,C2)

Regards
Bosco

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td]

5​

[/td][td]name[/td][td]value[/td][/tr]
[tr][td]

2​

[/td][td] Waffle[/td][td] 90[/td][td] [/td][td] Dog[/td][td] 30[/td][/tr]
[tr][td]

3​

[/td][td] Amy[/td][td] 50[/td][td] [/td][td] Cat[/td][td] 50[/td][/tr]
[tr][td]

4​

[/td][td] Amy[/td][td] 50[/td][td] [/td][td] Amy[/td][td] 50[/td][/tr]
[tr][td]

5​

[/td][td] Dog[/td][td] 30[/td][td] [/td][td] Stick[/td][td] 10[/td][/tr]
[tr][td]

6​

[/td][td] Stick[/td][td] 10[/td][td] [/td][td] Waffle[/td][td] 90[/td][/tr]
[tr][td]

7​

[/td][td][/td][td] [/td][td][/td][td][/td][td][/td][/tr]
[/table]

In C1 just enter:

=COUNT(E2:E6)

In A2 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$C$1,"",INDEX($D$2:$D$6,SMALL(IF($E$2:$E$6=$B2,ROW($D$2:$D$6)-ROW($D$2)+1),COUNTIFS($B$2:B2,B2))))

In B2 just enter and copy down:

=IF(ROWS($B$2:B2)>$C$1,"",LARGE($E$2:$E$6,ROWS($A$3:A3)))

 

[Aladin Akyurek]

Edit:

Replace

ROWS($A$3:A3)

with

ROWS($A$2:A2)

in post #4 .