Formulas for correctly ranking data in a table/list

I am trying to rank the following data:

Rank Team # of Wins Win %
1 Team A 5 100%
2 Team B 2 40%
3 Team C 1 50%
3 Team D 1 20%
3 Team E 1 50%

With The '# of Wins' column as the ranking factor. The ranking factor is pulling from a Pivot Table, where the criteria is the number of wins and losses by teams (in alphabetical order). The 'Team' column is an INDEX/MATCH formula, the 'Rank' column is a RANK formula, and the 'Win %' column is a VLOOKUP formula.

Unfortunately the 'Team' column is not pulling the way I want it to, and is showing this instead:

Rank Team # of Wins Win %
1 Team A 5 100%
2 Team B 2 40%
3 Team C 1 50%
3 Team C 1 50%
3 Team C 1 50%

How do I write a new formula for the 'Team' column so that after Team C shows, the next two rows will correctly eliminate Team C from consideration and display Team D (and thus display Team E once Team D has been eliminated). Basically, each team should be shown once.

Perhaps you guys have another way of rewriting the entire table. Please let me know!

Thanks so much!

The Pivot Table ranking wouldn't help much in this case because I would only like the win data for the top 5 teams to be displayed. The Pivot Table has around 20 teams and includes the number of losses. Thanks!

Couldn't you use the "=LARGE" function?

http://office.microsoft.com/en-us/ex...in=HP010342656

I actually didn't realize it, but the data table I typed didn't come out clearly.
Column A:
Rank
1
2
3
3
3

Column B:
Team
Team A
Team B
Team C
Team D
Team E

Column C:
# of Wins
5
2
1
1
1

Column D:
Win %
100%
40%
50%
20%
50%

The '# of Wins' column is the ranking factor. I am indeed using a LARGE function to draw those numbers from a pivot table. My question comes from the 'Team' column. I would like for it to display how it is above, but instead it is showing like this:

Team
Team A
Team B
Team C
Team C
Team C

Is there a function I can use to show Team D and then Team E instead of the bottom two Team C's?

Thanks again!

Ah. Sorry, my misunderstanding. See attached. The team names column uses an array formula, so remember that means you don't just hit enter after you typed the equation, you hit ctrl + shift + enter. If you don't have the array brackets { } at the ends, then you only hit enter. Let me know if this is what you're looking for.

This is awesome!! Thank you! Do you know how to adjust the columns so that it pulls from an entire table array? Currently with what I am working with the formula which only pulls from columns that are next to each other (Column A and Column B). When I try to change to another column i.e; Column D I receive a #VALUE error. Thanks again!

Sorry, not exactly sure what you mean. The sample file I sent ranks the top 5 team names and scores listed in A3:B34, so it will check the scores for more than 30 teams. What exactly are you trying to change?

It might be easier if you just upload a sample file, which you can do by clicking on "Go Advanced" at the bottom right corner of the Quick Reply message box and clicking on the paper clip at the top of the advanced page.