Ranked items - Vlookup with Dupes

[AHALEY]

I have been stumped with how to accomplish this in a formula. What I have is a list of bowlers and scores. I have ranked each bowler based on their total series of games, and would like to VLOOKUP results into a nicely formatted final standings sheet. I know I can accomplish what I am asking by simply doing an auto-filter for the rank column, but I need to keep the score entry sheet in tact.

The issue arises when 2 people carry the same score and are ranked in the same spot. Sample data is below:


Rank Name game1 game2 game3
1 bob 100 100 100
1 steve 100 99 100
3 adam 99 99 99



I am using the rank function to sort the bowlers, then sort the results sheet automatically using the =SMALL function. Then I am looking to Vlookup the name of the bowler based on the ranking on the new sheet. However, when I have the above situation, I get bob twice instead of bob and steve.

Any ideas how I can bring in different names for the same rank?

 

[T. Valko]

I have been stumped with how to accomplish this in a formula. What I have is a list of bowlers and scores. I have ranked each bowler based on their total series of games, and would like to VLOOKUP results into a nicely formatted final standings sheet. I know I can accomplish what I am asking by simply doing an auto-filter for the rank column, but I need to keep the score entry sheet in tact.

The issue arises when 2 people carry the same score and are ranked in the same spot. Sample data is below:


Rank Name game1 game2 game3
1 bob 100 100 100
1 steve 100 99 100
3 adam 99 99 99



I am using the rank function to sort the bowlers, then sort the results sheet automatically using the =SMALL function. Then I am looking to Vlookup the name of the bowler based on the ranking on the new sheet. However, when I have the above situation, I get bob twice instead of bob and steve.

Any ideas how I can bring in different names for the same rank?

One way...

Book1
	A	B	C	D	E	F	G
1	Rank	Name	Game1	Game2	Game3	Series Total	Adjusted Score
2	1	bob	100	100	100	300	299.999998
3	1	steve	100	100	100	300	299.999997
4	3	adam	99	99	99	297	296.999996
Sheet1

You can make the total scores unique then do the lookup on the highest values of this new score.

Enter this formula G2 and copy down:

=SUM(C2:E2)-ROW()/1000000

Then, you can lookup the highest ranked bowler like this:

Book1
	A	B
10	Name	Rank
11	bob	1
12	steve	1
13	adam	3
Sheet1

Enter this formula in A11 and copy down as needed:

=INDEX(B$2:B$4,MATCH(LARGE(G$2:G$4,ROWS(A$11:A11)),G$2:G$4,0))

Enter this formula in B11 and copy down as needed:

=INDEX(A$2:A$4,MATCH(A11,B$2:B$4,0))

 

[markmzz]

Or try this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Rank</td><td style=";">Name</td><td style=";">game1</td><td style=";">game2</td><td style=";">game3</td><td style="text-align: right;;"></td><td style=";">Rank</td><td style=";">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">bob</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">bob</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">steve</td><td style="text-align: right;;">100</td><td style="text-align: right;;">99</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">steve</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style=";">adam</td><td style="text-align: right;;">99</td><td style="text-align: right;;">99</td><td style="text-align: right;;">99</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style=";">adam</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style="text-align: right;;"></td><td style=";">******</td><td style=";">******</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Rank</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">{=INDEX(<font color="Blue">A$2:A$4,MATCH(<font color="Red">SMALL(<font color="Green">A$2:A$4+(<font color="Purple">ROW(<font color="Teal">A$2:A$4</font>)/100</font>),ROWS(<font color="Purple">$1:1</font>)</font>),A$2:A$4+(<font color="Green">ROW(<font color="Purple">A$2:A$4</font>)/100</font>),0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">{=INDEX(<font color="Blue">B$2:B$4,MATCH(<font color="Red">SMALL(<font color="Green">A$2:A$4+(<font color="Purple">ROW(<font color="Teal">A$2:A$4</font>)/100</font>),ROWS(<font color="Purple">$1:1</font>)</font>),A$2:A$4+(<font color="Green">ROW(<font color="Purple">A$2:A$4</font>)/100</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz