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

[Steviefiege]

Hi,

I've created a table below:

GOALKEEPER

COMPLETE WING BACK (RIGHT)

CENTRAL DEFENDER

COMPLETE WING BACK (LEFT)

ANCHOR MAN

ADVANCED PLAYMAKER

ROAMING PLAYMAKER

RAUMDEUTER (RIGHT)

RAUMDEUTER (LEFT)

DEFENSIVE FORWARD

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

PLAYER

RATING

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

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

Houghton, Jordan	Central Defender	47.91667
Houghton, Jordan	Anchor Man	48.18182
Houghton, Jordan	Advanced Playmaker	53.07692
Houghton, Jordan	Roaming Playmaker	51.59091

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

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.