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,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,2),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,3),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,4),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,5),$I$2:$BW$1000,67,FA LSE)

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.

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.

--
Biff
Microsoft Excel MVP


"Chad Portman" wrote in message
...
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,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,2),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,3),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,4),$I$2:$BW$1000,67,FA LSE)
=VLOOKUP(LARGE($I$2:$I$1000,5),$I$2:$BW$1000,67,FA LSE)

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.