Excel: Get Top 3 Values and Names

4
votes

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.

desiredresult

Here's what I get using RANK.

rank

Here's what I get using SORT.

sort

Here's what I get using LARGE

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.

4
excelsortingrank
ive tried all your suggested formulas and they all worked. thank you so much! Im wondering how i could get a list of names that got the 3 highest scores. i didnt ask it in my post because i thought it would be easy to do it using VLOOKUP then i realized VLOOKUP only returns the first result. - KarenDP
i have just updated my post. If anyone could please help me again. thanks in advance - KarenDP
See edit of my post. - basic
See edit of my post. - Scott Craner

4 Answers

6
votes

Option with AGGREGATE function:

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

enter image description here

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))),"")

enter image description here

3
votes

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

enter image description here

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))}
3
votes

If one has the Dynamic Array formula:

=LARGE(UNIQUE(B1:B10),SEQUENCE(3))

enter image description here

To get the names, use TEXTJOIN:

=TEXTJOIN(",",TRUE,IF($B$1:$B$10=C1,$A$1:$A$10,""))

enter image description here

0
votes

With a modern version of Excel with Dynamic arrays, you can use:

For the Scores:

D2: =LARGE(UNIQUE(Score),{1,2,3})

For the Analysts:

D3: =INDEX(Analyst,-1+AGGREGATE(15,6,1/(Score=D$2)*ROW(Score),ROW(INDEX($A:$A,1,1):INDEX($A:$A,COUNTIF(Score,D$2),1))))

enter image description here