Creating a baseball lineup based on averages and player preferences.
 

I'm coaching my softball team this year and have decided to determine the
lineup based purely on performance and individual player preference. Each
player has given me their ranked preference for thier position in the
batting order. They will only be competing agains others who have their
preference for any given position.

The worksheet looks like this (shortened for ease of example):
First row is the player name, second row is the player's batting average.

P1 P2 P3 P4 P5 P6
.10 .30 .40 .70 .20 .50
1 5 1 6 1 6 3
2 1 2 5 2 5 2
3 2 3 4 3 4 1
4 3 6 1 4 1 6
5 4 4 2 5 2 5
6 6 5 3 6 3 4

Rules:
A player wins a position if...
1) They are the single highest person remaining with a desire for the
position.
2) They have the best average vs anyone remaining at a given position.

The lineup should look like this:

1 P4
2 P1
3 P6
4 P3
5 P5
6 P2

I've been trying without success to accomplish this logic with a formula.
What would you guys do?

Thanks,
Jeremy


Sidenote: After every tournemant the players will be allowed to rerank their
preference list. Therefore, someone who has a high average but is
constantly up against someone better can choose change their top choices to
positions they would be more likely to win.