How to put the highest rated player in a cell for a particular position

[Steviefiege]

Hi,

I've created a table below:

[TABLE="width: 1240"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]GOALKEEPER[/TD]
[TD="colspan: 2"]COMPLETE WING BACK (RIGHT)[/TD]
[TD="colspan: 2"]CENTRAL DEFENDER[/TD]
[TD="colspan: 2"]COMPLETE WING BACK (LEFT)[/TD]
[TD="colspan: 2"]ANCHOR MAN[/TD]
[TD="colspan: 2"]ADVANCED PLAYMAKER[/TD]
[TD="colspan: 2"]ROAMING PLAYMAKER[/TD]
[TD="colspan: 2"]RAUMDEUTER (RIGHT)[/TD]
[TD="colspan: 2"]RAUMDEUTER (LEFT)[/TD]
[TD="colspan: 2"]DEFENSIVE FORWARD[/TD]
[/TR]
[TR]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[TD]PLAYER[/TD]
[TD]RATING[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
Here's what i want to do:
I have a range of players in another sheet, but for this i'll just put one player down. As you can see he can play in multiple positions & has different ratings for each of those. What i want to do is to put his highest rated position & rating in the table above in the relative cell. So the result should be that in the table he'll be under Advanced Playmaker with a rating of 53.07

[TABLE="width: 293"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Houghton, Jordan[/TD]
[TD]Central Defender[/TD]
[TD="align: right"]47.91667[/TD]
[/TR]
[TR]
[TD]Houghton, Jordan[/TD]
[TD]Anchor Man[/TD]
[TD="align: right"]48.18182[/TD]
[/TR]
[TR]
[TD]Houghton, Jordan[/TD]
[TD]Advanced Playmaker[/TD]
[TD="align: right"]53.07692[/TD]
[/TR]
[TR]
[TD]Houghton, Jordan[/TD]
[TD]Roaming Playmaker[/TD]
[TD="align: right"]51.59091[/TD]
[/TR]
</tbody>[/TABLE]

Rgs
Steve

 

[JackDanIce]

Hi Steve,

Without seeing your PC screen, can you provide some cell address/sheet names, things to help understand layout and where output should go (be specific, with cell address).

You've created the first table but no connection how it goes from top table to just selecting Houghton, Jordan and data changing from horizontal to vertical data.

As a general formula (you can adapt) try:

=INDEX($A$1:$A$20,MATCH(MAX($B$1:$B$20),$B$1:$B$20,0))

 

[Steviefiege]

Basically thats how its set out. The table is on a worksheet of its own called Squad Depth & the players are all in a list in a worksheet called Player Position Availability.
Just say the table starts in A1 with the positions going across the 1 row & the players the same starting in A1, there position in B1 & there rating in C1. What i want is for the player to go in his BEST RATED position in the table & once there then not be selected for the other positions he plays in as there not his best.