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

---

A	B	C	D	E
Waffle	90	1	Dog	30
Amy	50	2	Cat	50
Amy	50	3	Amy	50
Dog	30	4	Stick	10
Stick		5	Waffle	90

<tbody>
</tbody>

 

[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]

Row\Col	A​	B​	C​	D​	E​
1​			5​	name	value
2​	Waffle	90		Dog	30
3​	Amy	50		Cat	50
4​	Amy	50		Amy	50
5​	Dog	30		Stick	10
6​	Stick	10		Waffle	90
7​

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 .