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.

PLAYER	POSITION	RATING				PLAYER		RATING	BEST POSITION
Frazer Richardson	CWB (Right)					Aaron Taylor-Sinclair		59.29%
Gary Taylor-Fletcher	AVP	66.67%				Andrew Williams
Paul Keegan	AVP	65.00%				Andy Butler
Gary Taylor-Fletcher	DLP	63.89%				Benjamin Greasley
Mathieu Baudry	BPD	62.35%				Cedric Evina
Paul Keegan	DLP	62.22%				Conner Williamson
Tommy Rowe	CWB (Left)	61.67%				Craig Alcock
Tommy Rowe	AVP	61.11%				Dany N'Guessan
Andy Butler	BPD	60.88%				David Foley
Andrew Williams	Winger (Right)	60.45%				Dean Winnard
Gary Taylor-Fletcher	CF	60.23%				Frazer Richardson
Craig Alcock	BPD	60.00%				Gary McSheffrey
Aaron Taylor-Sinclair	CWB (Left)	59.29%				Gary Taylor-Fletcher
Tommy Rowe	IF (Left)	59.29%				Harry Middleton
Aaron Taylor-Sinclair	BPD	59.12%				Jai Quitongo
Tommy Rowe	DLP	58.33%				Joe Pugh
Gary Taylor-Fletcher	Winger (Right)	58.18%				Joe Wright
Cedric Evina	CWB (Left)	58.10%				John Marquis
Andrew Williams	IF (Left)	56.79%				Jordan Houghton
Jordan Houghton	DLP	56.67%				Jordan Linley
David Foley	Winger (Right)	56.36%				Joseph McCormick

<tbody>
</tbody>

Kind Regards
Steve

 

[jtakw]

Hi,

This is one way, Array Formula to be confirmed by CSE (Control, Shift, Enter):

Book1
	A	B	C	D	E	F	G	H	I	J
1	PLAYER	POSITION	RATING				PLAYER		RATING	BEST POSITION
2
3	Frazer Richardson	CWB (Right)					Aaron Taylor-Sinclair		59.29%	CWB (Left)
4	Gary Taylor-Fletcher	AVP	66.67%				Andrew Williams
5	Paul Keegan	AVP	65.00%				Andy Butler
6	Gary Taylor-Fletcher	DLP	63.89%				Benjamin Greasley
7	Mathieu Baudry	BPD	62.35%				Cedric Evina
8	Paul Keegan	DLP	62.22%				Conner Williamson
9	Tommy Rowe	CWB (Left)	61.67%				Craig Alcock
10	Tommy Rowe	AVP	61.11%				Dany N'Guessan
11	Andy Butler	BPD	60.88%				David Foley
12	Andrew Williams	Winger (Right)	60.45%				Dean Winnard
13	Gary Taylor-Fletcher	CF	60.23%				Frazer Richardson
14	Craig Alcock	BPD	60.00%				Gary McSheffrey
15	Aaron Taylor-Sinclair	CWB (Left)	59.29%				Gary Taylor-Fletcher
16	Tommy Rowe	IF (Left)	59.29%				Harry Middleton
17	Aaron Taylor-Sinclair	BPD	59.12%				Jai Quitongo
18	Tommy Rowe	DLP	58.33%				Joe Pugh
19	Gary Taylor-Fletcher	Winger (Right)	58.18%				Joe Wright
20	Cedric Evina	CWB (Left)	58.10%				John Marquis
21	Andrew Williams	IF (Left)	56.79%				Jordan Houghton
22	Jordan Houghton	DLP	56.67%				Jordan Linley
23	David Foley	Winger (Right)	56.36%				Joseph McCormick
Sheet214

Cell Formulas
Range	Formula
J3	{=INDEX(B3:B23,MATCH(G3&I3,A3:A23&C3:C23,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Steviefiege]

Thank you very much, thats worked a treat.

 

[jtakw]

You're welcome, welcome to the forum.

 

[Steviefiege]

Regarding my spreadsheet above. I can't remember how i did the formula in the right hand column for the rating (59.29%). It's just literally getting the highest number on the left hand column for that particular player. Can anyone help please?

 

[jtakw]

That's also going to require an Array formula:

Book1
	A	B	C	D	E	F	G	H	I	J
1	PLAYER	POSITION	RATING				PLAYER		RATING	BEST POSITION
2
3	Frazer Richardson	CWB (Right)					Aaron Taylor-Sinclair		59.29%	CWB (Left)
4	Gary Taylor-Fletcher	AVP	66.67%				Andrew Williams		60.45%	Winger (Right)
5	Paul Keegan	AVP	65.00%				Andy Butler		60.88%	BPD
6	Gary Taylor-Fletcher	DLP	63.89%				Benjamin Greasley		0.00%	#N/A
7	Mathieu Baudry	BPD	62.35%				Cedric Evina		58.10%	CWB (Left)
8	Paul Keegan	DLP	62.22%				Conner Williamson		0.00%	#N/A
9	Tommy Rowe	CWB (Left)	61.67%				Craig Alcock		60.00%	BPD
10	Tommy Rowe	AVP	61.11%				Dany N'Guessan		0.00%	#N/A
11	Andy Butler	BPD	60.88%				David Foley		56.36%	Winger (Right)
12	Andrew Williams	Winger (Right)	60.45%				Dean Winnard		0.00%	#N/A
13	Gary Taylor-Fletcher	CF	60.23%				Frazer Richardson		0.00%	#N/A
14	Craig Alcock	BPD	60.00%				Gary McSheffrey		0.00%	#N/A
15	Aaron Taylor-Sinclair	CWB (Left)	59.29%				Gary Taylor-Fletcher		66.67%	AVP
16	Tommy Rowe	IF (Left)	59.29%				Harry Middleton		0.00%	#N/A
17	Aaron Taylor-Sinclair	BPD	59.12%				Jai Quitongo		0.00%	#N/A
18	Tommy Rowe	DLP	58.33%				Joe Pugh		0.00%	#N/A
19	Gary Taylor-Fletcher	Winger (Right)	58.18%				Joe Wright		0.00%	#N/A
20	Cedric Evina	CWB (Left)	58.10%				John Marquis		0.00%	#N/A
21	Andrew Williams	IF (Left)	56.79%				Jordan Houghton		56.67%	DLP
22	Jordan Houghton	DLP	56.67%				Jordan Linley		0.00%	#N/A
23	David Foley	Winger (Right)	56.36%				Joseph McCormick		0.00%	#N/A
Sheet214

Cell Formulas
Range	Formula
I3	{=MAX(IF(A$3:A$23=G3,C$3:C$23))}
J3	{=INDEX(B$3:B$23,MATCH(G3&I3,A$3:A$23&C$3:C$23,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Steviefiege]

Thank you so much for that, kind of getting there now but still stuck on some more bits. See if i can explain this...
This is my page which automatically picks my squad Sheet 1 (well it will be when its done!)
I've sorted the formula for the rating (58.50). What i want is to match it to the player with that rating in that position which i have got on another sheet, Sheet 2. I'll add it underneath.

Sheet 1

Position		Player		Rating
WB (Right)				58.50
CD
CD
WB (Left)
BBM
BBM
IF (Right)
IF (Left)
F9
F9

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2 Alex Kiwomya	49.64	IF (Right)
Alfie Beestin	52.14	IF (Right)
Alfie May	50.71	IF (Left)
Andrew Williams	53.46	F9
Andy Boyle	68.33	CD
Andy Butler	70.83	CD
Anthony Greaves	30.00	IF (Right)
Ben Whiteman	54.21	BBM
Branden Horton	46.25	CD
Cameron Barnett	36.92	F9
Cedric Evina	47.00	WB (Left)
Corie Andrews	50.00	IF (Right)
Craig Alcock	60.42	CD
Danny Andrew	61.00	WB (Left)
Henrik Bjordal	55.53	BBM
Issam Ben Khemis	48.95	BBM
James Coppinger	60.71	IF (Right)
Joe Wright	56.25	CD
John Marquis	54.23	F9
Jordan Houghton	51.58	BBM
Liam Mandeville	56.92	F9
Luke McCullough	57.92	CD
Mathieu Baudry	60.00	CD
Matty Blair	58.50	WB (Right)
Mitchell Lund	53.50	WB (Right)
Myron Gibbons	32.69	F9
Niall Mason	56.50	WB (Right)
Rieves Boocock	36.15	F9
Rodney Kongolo	57.08	CD
Shane Blaney	49.58	CD
Tom Anderson	56.25	CD
Tommy Rowe	59.50	WB (Left)
Tyler Garrett	54.00	WB (Left)

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The player in bold in sheet 2 is the player that wants to be in sheet 1 in the WB (Right) position, i just cant get my head around the formula.

Rgs
Steve

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

Book1
	A	B	C	D	E
1	Position		Player		Rating
2
3	WB (Right)		Matty Blair		58.5
4	CD
5	CD
6	WB (Left)
7
8	BBM
9	BBM
10
11	IF (Right)
12	IF (Left)
13
14	F9
15	F9
Sheet220

Cell Formulas
Range	Formula
C3	{=INDEX(Sheet221!A$1:A$33,MATCH(A$3&E$3,Sheet221!C$1:C$33&Sheet221!B$1:B$33,0))}
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

Change cell references/range and sheet name to match your data.

 

[Steviefiege]

Thank you soo soo very much. Another question, Where i have 2 x CD & 2 x BBM & 2 x F9, how do i get the 2nd highest rated player there?

 

[jtakw]

That's going to require a different formula.
So your actual setup is like my Post # 8 above?
Are there actually empty rows (in between different positions) as shown in Post # 8, or not?