Excel: Get Top 3 Values and Names

Question

I am trying to get the top 3 distinct Scores(result of a formula) as well as the names of the analysts who got those (3 highest)scores. I've tried using RANK, SORT, LARGE and all give me weird results.

This is the result I am going for. Note that the number of analysts per score varies.

Here's what I get using RANK.

Here's what I get using SORT.

Here's what I get using LARGE

I am not sure what I am doing wrong. Maybe I'm using the wrong function so I'd appreciate a lot if anyone can point me to the right direction.

Answer 1

Option with AGGREGATE function:

=AGGREGATE(14,6,($B$2:$B$11)*(COUNTIF($D$1:D1,$B$2:$B$11)=0),1)

Additionally, to get names:

=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,(1/($D2=$B$2:$B$11))*ROW($B$2:$B$11)-1,COLUMN()-COLUMN($D$2))),"")

Answer 2

Here is a solution using MAX. The second and third are array formulas.

Get the largest value:

=MAX(C2:C11)

Get the largest value that is smaller than the value above (E2 is the cell above):

{=MAX(IF($C$2:$C$11<E2,$C$2:$C$11))}

Get the largest value that is smaller than the value above (E3 is the cell above):

{=MAX(IF($C$2:$C$11<E3,$C$2:$C$11))}