Returning the top 3 and bottom 2 values from an array

simonoma

New Member
Joined
Jul 11, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello there! Very basic user here, so appreciate your advice.

I have a list of 10 people which are ranked and I want to return the 3 top performers and the two bottom performers for that month.

The complicated part is that some of the rankings are equal. When there are equal rank values, the people are already sorted by ability so if two people get the same rank, the higher value will be awarded to the first person on the list (I know it sounds unfair 😁).

So if this was the table:

Name: Rod, Jane, Freddy, Geoffrey, Bungle
Test 1 Rank: 1, 5, 3, 3, 3
Test 2 Rank: 2, 3, 5, 1, 3

I would want the results for Test 1 to show Rod, Freddy and Geoffrey as the top 3 performers. Even though Bungle got an equal score, he is lower down the list, so in fact, he is the second lowest performer, with Jane as bottom.
For Test 2, Geoffrey, Rod and Jane are top 3. Bungle comes 4th and Freddy, 5th.

Attached is a closer table to what I am hoping to create, with 10 people in total, with the top 3 and bottom two values returned for each month. I am really sorry but I can't seem to install XL2BB so i only have a picture.

Is this possible with formulas (I know LARGE and SMALL but my silly, complicated ranking system doesn't allow for equals and I don't know how to return the value at the top in Row 2)?

Thank you for any help or guidance!
Simon
 

Attachments

  • Rank Help.png
    Rank Help.png
    14.2 KB · Views: 9

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi & welcome to MrExcel.
How about
Excel Formula:
=INDEX(SORTBY(B2:K2,B3:K3),{1,2,3,9,10})
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top