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:

Coppinger, James	Advanced Playmaker	57.00
Coppinger, James	Advanced Playmaker	57.00

<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>
</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
1	Name	Position	Rating			Name	Position	Rating	Row
2	Al	First	33			Coppinger, James	Advanced Playmaker	57	3
3	Coppinger, James	Advanced Playmaker	57			Kiwomya, Alex	Inside Forward (Left)	57	5
4	Bob	Second	44			Bob	Second	44	4
5	Kiwomya, Alex	Inside Forward (Left)	57			Al	First	33	2
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IF(H2="","",INDEX(A:A,$I2)) G2 =IF(I2="","",INDEX(B:B,$I2)) H2 =IFERROR(LARGE(C$2:C$10,ROWS(H$2:H2)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I2 {=IF(H2="","",SMALL(IF(C$2:C$10=H2,ROW(C$2:C$10)),COUNTIF(H$2:H2,H2)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.