CODEWITHSUNDEEP
excelnumbersmatch
Sandra Maksvytytė
Sandra Maksvytytė

Reputation: 3

EXCEL Get top 3 largest numbers in repetitive array

enter image description hereI have an array of people with scores in other column. I need to find top 3 people with highest score and print their names.

Example:

Maria  1
Thomas 4
John   3
Jack   2
Ray    2
Laura  4
Kate   3

Result should be:

Thomas
Laura 
John

What I get:

Thomas
Thomas
John

What I get:

Thomas
John

num

I have tried using LARGE, MATCH, MIN, MAX but nothings works.

My first failure code:

=INDEX($A$2:$A$8;  MATCH(LARGE(($B$2:$B$8);{1;2;3}); $B$2:$B$8;0))

My second failure code:

{=INDEX($A$2:$A$14;SMALL(IF($B$2:$B$14=MAX($B$2:$B$14);ROW($B$2:$B$14)-1);ROW(B4)-1))}

Upvotes: 0

Views: 335

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Put this in the second row of the column you want:

=INDEX(A:A,AGGREGATE(15,7,ROW($B$1:$B$7)/((COUNTIF($D$1:D1,$A$1:$A$7)=0)*($B$1:$B$7=LARGE(B:B,ROW(1:1)))),1))

And drag down three rows:

enter image description here

Upvotes: 1

Related Questions