Top 5 vs Bottom 5

[Chad Portman]

Here is the problem I have now I am trying to view the top 5 in a group of
data. I have been trying to use the formula as follows:

=VLOOKUP(LARGE($I$2:$I$1000,1),$I$2:$BW$1000,67,FALSE)
=VLOOKUP(LARGE($I$2:$I$1000,2),$I$2:$BW$1000,67,FALSE)
=VLOOKUP(LARGE($I$2:$I$1000,3),$I$2:$BW$1000,67,FALSE)
=VLOOKUP(LARGE($I$2:$I$1000,4),$I$2:$BW$1000,67,FALSE)
=VLOOKUP(LARGE($I$2:$I$1000,5),$I$2:$BW$1000,67,FALSE)

I have these in Cells BZ2-6.

Col I has the data that I need to be sorted into the top five and column BW
has the data I need as to what person that data belongs to which is the info
I need. The problem I have is this if two or more of the top 5 are the same
number I get the same name and I need 5 unique names with the 5 best scores
in Col I even if they all the same. If there are more then 5 that tie for the
highest which I doubt will ever happen but in case just a random five names
is fine. I will need this same info for the data in all Cols I-BV always
pulling the data from BW. Not only that but once done with that I need the
same thing but for the bottom 5 which I think if I can just make this work
changing Large to Small will do that much for me. Let me know if it is too
confusing and you need more info.

 

[T. Valko]

The easiest way to do this is to add a column that produces a unique rank.

Enter this formula in H2. This will give each number in column I a unique
rank.

=RANK(I2,I$2:I$20)+COUNTIF(I$2:I2,I2)-1

Copy down to the end of data in column I.

Enter this formula in A1. This will return the count of instances that are
within the top 5. A top 5 list may contain more than 5 values depending on
ties.

=COUNTIF(I2:I20,">="&LARGE(I2:I20,5))

Enter this formula in A2. This will return the names associated with the top
5:

=IF(ROWS(A$2:A2)<=A$1,INDEX(BW$2:BW$20,MATCH(SMALL(H$2:H$20,ROWS(A$2:A2)),H$2:H$20,0)),"")

Copy down until you get blanks.

Adjust the ranges to suit.