Getting the same value twice with VLOOKUP

[Steviefiege]

Hi,
I have a list of players & there rating & there all ranked highest to lowest. But, when there are 2 players that have the same rating it only shows the top one, how do i get it to show the next one?

Example:

<colgroup><col style="mso-width-source:userset;mso-width-alt:4534;width:93pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl67, width: 124"]Coppinger, James[/TD]
[TD="class: xl66, width: 157"]Advanced Playmaker[/TD]
[TD="class: xl68, width: 64"]57.00[/TD]

[TD="class: xl67"]Coppinger, James[/TD]
[TD="class: xl66"]Advanced Playmaker[/TD]
[TD="class: xl68"]57.00
[/TD]

</tbody>

The second one should be Kiwomya, Alex Inside Forward (Left) 57.00

Rgs
Steve

 

[Eric W]

One way:

	A	B	C	D	E	F	G	H	I	J
Name	Position	Rating	Name	Position	Rating	Row
Al	First	Coppinger, James	Advanced Playmaker
Kiwomya, Alex	Inside Forward (Left)
Bob	Second	Bob	Second
Kiwomya, Alex	Inside Forward (Left)	Al	First

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]57[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Coppinger, James[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Advanced Playmaker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]57[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]57[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(H2="","",INDEX(A:A,$I2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(I2="","",INDEX(B:B,$I2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IFERROR(LARGE(C$2:C$10,ROWS(H$2:H2)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=IF(H2="","",SMALL(IF(C$2:C$10=H2,ROW(C$2:C$10)),COUNTIF(H$2:H2,H2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

You can hide column I if you don't want to see it.

 

[Steviefiege]

Thank you ever ever ever so much, that has worked an absolute peach. Took me a while to twiddle the formula's around to match my sheet but my god what a godsend!!!

 

[Eric W]

Glad it works for you! Thanks for the feedback.