Matching 2 Cells to get the 3rd

[Steviefiege]

Hi, i'm hoping this is an easy one, i just can't get my head around the formula to use.
Below is my spreadsheet with all my players on the left & every position they play in & their ratings.
On the right is every individual player & their BEST rating. What i'm wanting is for on the right under BEST POSITION is to match the player & the rating to get their BEST POSITION.
For example: Aaron Taylor-Sinclair his best rating is 59.29%, under BEST POSITION i want it to say CWB (Left) as thats his best position but i cant get the formula right. I've put it in bold on the left as so you can see which one i mean.

<tbody>
[TD="class: xl63"]PLAYER[/TD]
[TD="class: xl63, width: 100"]POSITION[/TD]
[TD="class: xl63, width: 100"]RATING[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 130"]PLAYER[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 100"]RATING[/TD]
[TD="class: xl63, width: 64"]BEST POSITION[/TD]

[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl65"]Frazer Richardson[/TD]
[TD="class: xl66"]CWB (Right)[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Aaron Taylor-Sinclair[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl74"]59.29%[/TD]
[TD="class: xl75"][/TD]

[TD="class: xl67"]Gary Taylor-Fletcher[/TD]
[TD="class: xl68"]AVP[/TD]
[TD="class: xl64"]66.67%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Andrew Williams[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Paul Keegan[/TD]
[TD="class: xl66"]AVP[/TD]
[TD="class: xl64"]65.00%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Andy Butler[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl67"]Gary Taylor-Fletcher[/TD]
[TD="class: xl68"]DLP[/TD]
[TD="class: xl64"]63.89%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Benjamin Greasley[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Mathieu Baudry[/TD]
[TD="class: xl70"]BPD[/TD]
[TD="class: xl64"]62.35%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Cedric Evina[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Paul Keegan[/TD]
[TD="class: xl66"]DLP[/TD]
[TD="class: xl64"]62.22%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Conner Williamson[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Tommy Rowe[/TD]
[TD="class: xl66"]CWB (Left)[/TD]
[TD="class: xl64"]61.67%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Craig Alcock[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Tommy Rowe[/TD]
[TD="class: xl66"]AVP[/TD]
[TD="class: xl64"]61.11%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Dany N'Guessan[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Andy Butler[/TD]
[TD="class: xl70"]BPD[/TD]
[TD="class: xl64"]60.88%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl72"]David Foley[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Andrew Williams[/TD]
[TD="class: xl68"]Winger (Right)[/TD]
[TD="class: xl64"]60.45%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Dean Winnard[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl67"]Gary Taylor-Fletcher[/TD]
[TD="class: xl68"]CF[/TD]
[TD="class: xl64"]60.23%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Frazer Richardson[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Craig Alcock[/TD]
[TD="class: xl70"]BPD[/TD]
[TD="class: xl64"]60.00%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Gary McSheffrey[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"] Aaron Taylor-Sinclair
[/TD]
[TD="class: xl66"] CWB (Left) [/TD]
[TD="class: xl64"] 59.29% [/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl72"]Gary Taylor-Fletcher[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Tommy Rowe[/TD]
[TD="class: xl68"]IF (Left)[/TD]
[TD="class: xl64"]59.29%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Harry Middleton[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl65"]Aaron Taylor-Sinclair[/TD]
[TD="class: xl70"]BPD[/TD]
[TD="class: xl64"]59.12%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Jai Quitongo[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Tommy Rowe[/TD]
[TD="class: xl66"]DLP[/TD]
[TD="class: xl64"]58.33%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Joe Pugh[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl67"]Gary Taylor-Fletcher[/TD]
[TD="class: xl68"]Winger (Right)[/TD]
[TD="class: xl64"]58.18%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Joe Wright[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Cedric Evina[/TD]
[TD="class: xl66"]CWB (Left)[/TD]
[TD="class: xl64"]58.10%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]John Marquis[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Andrew Williams[/TD]
[TD="class: xl68"]IF (Left)[/TD]
[TD="class: xl64"]56.79%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl71"]Jordan Houghton[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl69"]Jordan Houghton[/TD]
[TD="class: xl66"]DLP[/TD]
[TD="class: xl64"]56.67%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Jordan Linley[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl67"]David Foley[/TD]
[TD="class: xl68"]Winger (Right)[/TD]
[TD="class: xl64"]56.36%[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl73"]Joseph McCormick[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]

</tbody>

Kind Regards
Steve

 

[jtakw]

As I have a busy day tomorrow and may not have time to respond, and you haven't answered my questions above.

Assuming the setup is like my Post # 8, use C3 and E3 formulas if there are No Blank rows.
Use D3 and F3 formulas if there are Blank rows in between different positions.

Book1
	A	B	C	D	E	F
1	Position		Player	Player	Rating	Rating
2
3	WB (Right)		Matty Blair	Matty Blair	58.5	58.5
4	CD		Andy Butler	Andy Butler	70.83	70.83
5	CD		Andy Boyle	Andy Boyle	68.33	68.33
6	WB (Left)		Danny Andrew	Danny Andrew	61	61
7			#NUM!		#NUM!
8	BBM		Henrik Bjordal	Henrik Bjordal	55.53	55.53
9	BBM		Ben Whiteman	Ben Whiteman	54.21	54.21
10			#NUM!		#NUM!
11	IF (Right)		James Coppinger	James Coppinger	60.71	60.71
12	IF (Left)		Alfie May	Alfie May	50.71	50.71
13			#NUM!		#NUM!
14	F9		Liam Mandeville	Liam Mandeville	56.92	56.92
15	F9		John Marquis	John Marquis	54.23	54.23
Sheet220

Cell Formulas
Range	Formula
C3	{=INDEX(Sheet221!A$1:A$33,MATCH(A3&E3,Sheet221!C$1:C$33&Sheet221!B$1:B$33,0))}
D3	{=IFERROR(INDEX(Sheet221!A$1:A$33,MATCH(A3&E3,Sheet221!C$1:C$33&Sheet221!B$1:B$33,0)),"")}
E3	{=LARGE(IF(Sheet221!C$1:C$33=A3,Sheet221!B$1:B$33),COUNTIF(A$3:A3,A3))}
F3	{=IFERROR(LARGE(IF(Sheet221!C$1:C$33=A3,Sheet221!B$1:B$33),COUNTIF(A$3:A3,A3)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C
1	Alex Kiwomya	49.64	IF (Right)
2	Alfie Beestin	52.14	IF (Right)
3	Alfie May	50.71	IF (Left)
4	Andrew Williams	53.46	F9
5	Andy Boyle	68.33	CD
6	Andy Butler	70.83	CD
7	Anthony Greaves	30	IF (Right)
8	Ben Whiteman	54.21	BBM
9	Branden Horton	46.25	CD
10	Cameron Barnett	36.92	F9
11	Cedric Evina	47	WB (Left)
12	Corie Andrews	50	IF (Right)
13	Craig Alcock	60.42	CD
14	Danny Andrew	61	WB (Left)
15	Henrik Bjordal	55.53	BBM
16	Issam Ben Khemis	48.95	BBM
17	James Coppinger	60.71	IF (Right)
18	Joe Wright	56.25	CD
19	John Marquis	54.23	F9
20	Jordan Houghton	51.58	BBM
21	Liam Mandeville	56.92	F9
22	Luke McCullough	57.92	CD
23	Mathieu Baudry	60	CD
24	Matty Blair	58.5	WB (Right)
25	Mitchell Lund	53.5	WB (Right)
26	Myron Gibbons	32.69	F9
27	Niall Mason	56.5	WB (Right)
28	Rieves Boocock	36.15	F9
29	Rodney Kongolo	57.08	CD
30	Shane Blaney	49.58	CD
31	Tom Anderson	56.25	CD
32	Tommy Rowe	59.5	WB (Left)
33	Tyler Garrett	54	WB (Left)
Sheet221