Using MATCH to link different sheets as one

[Steviefiege]

Hi, i'm new to this forum so i hope i've posted this in the right section.
What i'm doing is making a spreadsheet for my Football Manager game. I've got everything i need in, i just need to get a formula right to make my "Match Squad" work properly. Each position i've done with all the correct players but when you go to the match squad it puts the same players in different positions as they are the best player for that position, but what i want it to do is once the player is playing his in highest rated position for the spreadsheet not to pick him again so to pick the next one available. Some players though can play in multiple positions.

[TABLE="width: 549"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Goalkeeper:[/TD]
[TD][/TD]
[TD]Ross Etheridge[/TD]
[TD="align: right"]49.23%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ball Playing Defender:[/TD]
[TD][/TD]
[TD]Mathieu Baudry[/TD]
[TD="align: right"]62.35%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Andy Butler[/TD]
[TD="align: right"]60.88%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Complete Wing Back (Right):
[/TD]
[TD][/TD]
[TD]Frazer Richardson[/TD]
[TD="align: right"]58.81%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Complete Wing Back (Left):[/TD]
[TD][/TD]
[TD]Tommy Rowe[/TD]
[TD="align: right"]61.67%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deep Lying Playmaker:[/TD]
[TD][/TD]
[TD]Paul Keegan[/TD]
[TD="align: right"]62.22%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Playmaker:[/TD]
[TD][/TD]
[TD]Paul Keegan[/TD]
[TD="align: right"]65.00%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]James Coppinger[/TD]
[TD="align: right"]63.89%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winger (Right):[/TD]
[TD][/TD]
[TD]James Coppinger[/TD]
[TD="align: right"]65.45%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inside Forward (Left):[/TD]
[TD][/TD]
[TD]James Coppinger[/TD]
[TD="align: right"]59.29%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Complete Forward:[/TD]
[TD][/TD]
[TD]John Marquis[/TD]
[TD="align: right"]54.32%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you can see, my midfield consists of 2 players playing in all the positions, but i only want them playing in their highest rated position.
I really help someone can help me. I tried attaching the spreadsheet but it won't allow me, is that because i'm new?

Kind Regards
Steve

 

[RasGhul]

Hi Steve,

Not sure if you can use this but, you could use the following to find/match the best position to the player list based on your player data.

Note that some positions are missing as there is no other higher rated players in the data set;

Book1
	A	B	C
1	PLAYER DATA		Ratings
2	Goalkeeper:	Ross Etheridge	49.23%
3	Ball Playing Defender:	Mathieu Baudry	62.35%
4	Complete Wing Back (Right):	Frazer Richardson	58.81%
5	Complete Wing Back (Left):	Tommy Rowe	61.67%
6	Deep Lying Playmaker:	Paul Keegan	62.22%
7	Advanced Playmaker:	Paul Keegan	65.00%
8		James Coppinger	63.89%
9	Winger (Right):	James Coppinger	65.45%
10	Inside Forward (Left):	James Coppinger	59.29%
11	Complete Forward:	John Marquis	54.32%
12
13	BEST POSITION
14	Goalkeeper:	Ross Etheridge	49.23%
15	Ball Playing Defender:	Mathieu Baudry	62.35%
16	Complete Wing Back (Right):	Frazer Richardson	58.81%
17	Complete Wing Back (Left):	Tommy Rowe	61.67%
18	Advanced Playmaker:	Paul Keegan	65.00%
19	Winger (Right):	James Coppinger	65.45%
20	Complete Forward:	John Marquis	54.32%
Sheet1

Cell Formulas
Range	Formula
A14	=INDEX($A$2:$A$11,MATCH(B14&C14,INDEX($B$2:$B$11&$C$2:$C$11,),0))
C14	{=MAX(IF($B$2:$B$11=B14,$C$2:$C$11))}
Press CTRL+SHIFT+ENTER to enter array formulas.