Ranking with duplicates

[Imayneedhelpwithexcel]

I am working on a sheet where I have to show the ranking of the top 5 people with lowest number of reports written. I want to make it so that everytime we update the "Report Written" column it automatically shows the top 5 lowest number reports written people. At first i thought i got it with the INDEX, MATCH and SMALL function but if we consider the fact that there may be people with the same number of reports then it just repeats the first person excel sees as lowest twice. How do i get rid of this duplicate?

 

[aaewalsh]

You can use the INDEX and MATCH functions together with the RANK function to achieve this. The RANK function will assign a unique rank to each person based on the number of reports they have written, even if there are ties. Then, you can use INDEX and MATCH to retrieve the names of the top 5 ranked people.

Here's an example of how you can set this up:

1. Create a separate column for the ranks next to the "Report Written" column.
2. In the first cell of the "Rank" column, enter the formula
   

   =RANK(A2,$A$2:$A$10)

   where A2 is the cell reference for the first person's number of reports written and A2:A10 is the range of cells containing all of the people's number of reports written.
3. Drag the formula down to fill the rest of the "Rank" column.
4. In another column, use INDEX and MATCH function to retrieve the names of the top 5 ranked people.

=INDEX(B:B,MATCH(1,(RANK(A:A,$A$1:$A$10)=1:5),0))

Where B:B is the range of cells containing all of the people's names and A:A is the range of cells containing all of the people's number of reports written.

By using RANK function, it will assign a unique number to each person, even if there are ties. And by using INDEX and MATCH function, it will retrieve the names of the top 5 people with the lowest number of reports written.

 

[kvsrinivasamurthy]

In F2 then copy down

=IFERROR(INDEX($A$2:$A$12,AGGREGATE(15,6,ROW($A$2:$A$12)/(($B$2:$B$12=SMALL($B$2:$B$12,E2))*(ISERROR(MATCH($A$2:$A$12,$F$1:$F1,0)))),1)-ROW($A$1)),"")