Excel Index Match - Largest, 2nd Largest, 3rd Largest without duplicate Matches-CodePudding

Really appreciate any help. I'm trying to match the highest, 2nd highest, and 3rd highest scoring sections from a list without duplicates. But I am stuck with how to index the results.

Right now because multiple sections may have a score of 100%, if there are two sections with 100%, match will immediately index the first result, instead of the next unique result.

Current Formulas:

=INDEX(D6:D14,MATCH(LARGE(E6:E14,1),E6:E14,0))
=INDEX(D6:D14,MATCH(LARGE(E6:E14,2),E6:E14,0))
=INDEX(D6:D14,MATCH(LARGE(E6:E14,3),E6:E14,0))
=INDEX(D6:D14,MATCH(SMALL(E6:E14,1),E6:E14,0))
=INDEX(D6:D14,MATCH(SMALL(E6:E14,2),E6:E14,0))
=INDEX(D6:D14,MATCH(SMALL(E6:E14,3),E6:E14,0))

Current Data

Data

Description	Score (%)
RED	100
BLUE	30
GREEN	100
WHITE	100
ORANGE	99
PURPLE	75
CYAN	75
BLACK	80
GRAY	37

Answers

Rank	Description
Highest	RED
2nd Highest	RED
3rd Highest	RED
Lowest	BLUE
2nd Lowest	GRAY
3rd Lowest	PURPLE

Desired Data:

2nd Highest to be Green
3rd Highest to be White

CodePudding user response：

With Office 365 we can use SORT and CHOOSE the top three and bottom three:

=LET(
    rng,    A2:B10,
    rws,    ROWS(rng),
    INDEX(SORT(rng,2,-1),CHOOSE({1;2;3;4;5;6},1,2,3,rws,rws-1,rws-2),1))

The top three will go in order of entry if tied, the bottom three will go from the bottom up if tied.

CodePudding user response：

If you are sure there would at least always be six values, you could try:

Formula in E2:

=LET(X,SORT(A2:B10,2,-1),Y,SEQUENCE(6),INDEX(X,IF(Y<4,Y,COUNTA(X)/2-(Y-4)),1))