Google sheets formula to get the Top 5 List With Duplicates-CodePudding

I'm trying to compile a best 5 and worst 5 list. I have two rows, column B with the number score and column C with the name. I only want the list to include the name.

In my previous attempts the formula would get the top/bottom 5 but as soon as a duplicate score appeared the first known name with that value would just repeat.

Here is my data

26 Cal
55 John
55 Mike
100 Steve
26 Thomas
100 Jaden
100 Jack
95 Josh
87 Cole
75 Brett

I've managed to get the bottom 5 list formula correct. This formula works perfectly and includes all names of duplicate scores.

Example of what I get:

Cal
Thomas
John
Mike
Brett

=INDEX($C$56:$E$70,SMALL(IF($B$56:$B$70=SMALL($B$56:$B$70,ROWS(E$2:E2)),ROW($B$56:$B$70)-ROW($B$56) 1),SUM(IF($B$56:$B$70=SMALL($B$56:$B$70,
  ROWS(E$2:E2)),1,0))-SUM(IF($B$56:$B$70<=SMALL($B$56:$B$70,ROWS(E$2:E2)),1,0)) ROWS(E$2:E2)))

Here is the formula I've tried to get the top 5 - however I keep getting an error.

=INDEX($C$56:$E$70,LARGE(IF($B$56:$B$70=LARGE($B$56:$B$70,ROWS(E$2:E2)),ROW($B$56:$B$70)-ROW($B$56) 1),SUM(IF($B$56:$B$70=LARGE($B$56:$B$70,
  ROWS(E$2:E2)),1,0))-SUM(IF($B$56:$B$70<=LARGE($B$56:$B$70,ROWS(E$2:E2)),1,0)) ROWS(E$2:E2)))

Example of what I'm looking for

Steve
Jaden
Jack
Josh
Cole

CodePudding user response：

You can set two queries like this for both cases:

=QUERY(B56:C70,"Select C order by B desc limit 5")
=QUERY(B56:C70,"Select C order by B limit 5")

CodePudding user response：

Use SORTN() function like-

=SORTN(A1:B10,5,,1,1)

To keep only one column, wrap the SORTN() function with INDEX() and specify column number. Try-

=INDEX(SORTN(A1:B10,5,,1,1),,2)