Matching one position to another

[Steviefiege]

Hi,

At the top of my page i have this:

<colgroup><col width="220"><col width="152"><col width="157"><col width="118"></colgroup><tbody>
[TD="class: xl70, width: 220"]Highest Rated Player (Available):[/TD]
[TD="class: xl67, width: 152"]Beestin, Alfie[/TD]
[TD="class: xl67, width: 157"]Attacking Midfielder[/TD]
[TD="class: xl69, width: 118"]500
[/TD]

</tbody>

[TABLE="width: 647"]
<colgroup><col width="220"><col width="152"><col width="157"><col width="118"></colgroup><tbody>[TR]
[TD="class: xl67, width: 157"]At the side i have this:

[TABLE="width: 148"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Goalkeeper[/TD]
[/TR]
[TR]
[TD]Full Back (Right)[/TD]
[/TR]
[TR]
[TD]Central Defender[/TD]
[/TR]
[TR]
[TD]Defensive Centre Back[/TD]
[/TR]
[TR]
[TD]Full Back (Left)[/TD]
[/TR]
[TR]
[TD]Advanced Playmaker[/TD]
[/TR]
[TR]
[TD]Advanced Playmaker[/TD]
[/TR]
[TR]
[TD]Inside Forward (Right)[/TD]
[/TR]
[TR]
[TD]Attacking Midfielder
[/TD]
[/TR]
[TR]
[TD]Inside Forward (Left)[/TD]
[/TR]
[TR]
[TD]Advanced Forward[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl69, width: 118"][/TD]
[/TR]
</tbody>[/TABLE]

What MATCH formula do i need to use to get him to go at the side of his correct position? (In bold)

Rgs
Steve

 

[Scott T]

[TABLE="class: grid, width: 500"]
<COLGROUP><COL style="BACKGROUND-COLOR: rgb(218,231,245)"><COL><COL><COL><COL></COLGROUP><TBODY>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Highest Rated Player (Available):[/TD]
[TD]Beestin, Alfie[/TD]
[TD]Attacking Midfielder[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Goalkeeper[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Full Back (Right)[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Central Defender[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Defensive Centre Back[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Full Back (Left)[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Advanced Playmaker[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Advanced Playmaker[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Inside Forward (Right)[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Attacking Midfielder[/TD]
[TD]Beestin, Alfie[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Inside Forward (Left)[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Advanced Forward[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]

Sheet2​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas [TABLE="class: grid, width: 100%"]
<TBODY>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(A4=$C$1,$B$1,"")[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

and copy down

Note the positions must match exactly in spacing and letters so you can not just have full back in C1 you would need full back (right) or full back (left)

 

[Steviefiege]

Thank you, didn't think it was as easy as that

 

[Steviefiege]

How would i do this?.....
Here is a list of my players, positions & rating: (It's not them all, just a few) & below that is my teamsheet

[TABLE="width: 345"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Boyle, Andy[/TD]
[TD]Central Defender[/TD]
[TD]64.71[/TD]
[/TR]
[TR]
[TD]Butler, Andy[/TD]
[TD]Central Defender[/TD]
[TD]62.06[/TD]
[/TR]
[TR]
[TD]Boyle, Andy[/TD]
[TD]Defensive Centre Back[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]Butler, Andy[/TD]
[TD]Defensive Centre Back[/TD]
[TD]61.2[/TD]
[/TR]
[TR]
[TD]Andrew, Danny
[/TD]
[TD]Full Back (Left)[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]Lawlor, Ian
[/TD]
[TD]Goalkeeper[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Rowe, Tommy[/TD]
[TD]Full Back (Left)[/TD]
[TD]59.5[/TD]
[/TR]
[TR]
[TD]Coppinger, James[/TD]
[TD]Attacking Midfielder[/TD]
[TD]59[/TD]
[/TR]
[TR]
[TD]Blair, Matty[/TD]
[TD]Full Back (Right)[/TD]
[TD]58.5[/TD]
[/TR]
[TR]
[TD]Rowe, Tommy[/TD]
[TD]Inside Forward (Left)[/TD]
[TD]58.1[/TD]
[/TR]
[TR]
[TD]Alcock, Craig[/TD]
[TD]Full Back (Right)[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]Marquis, John
[/TD]
[TD]Advanced Forward[/TD]
[TD]57.38[/TD]
[/TR]
[TR]
[TD]Coppinger, James
[/TD]
[TD]Advanced Playmaker[/TD]
[TD]57
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 240"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Goalkeeper[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Back (Right)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Central Defender[/TD]
[TD]Boyle, Andy[/TD]
[/TR]
[TR]
[TD]Defensive Centre Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full Back (Left)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Playmaker[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Playmaker[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inside Forward (Right)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attacking Midfielder[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Inside Forward (Left)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced Forward
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What i'm wanting is for example, Andy Boyle who's in the team at Central Defender is now used & the position also. I want all the players in the top list who have either been selected (Andy Boyle) or position selected (Central Defender) to not be able to be picked again for any other position. Does that make sense?

Steve