Excel problem - looking for a solution

[vaskas]

Good afternoon dear friends,

I have a difficult case i am trying to figure out in excel, but i do not seem to get there yet. I would appreciate your thoughts and ideas!

Let me explain you the scenario:

1) Assume we have 2 teams ("My Team" and "Opponent Team"), with players and corresponding player levels
2) Each player is assigned to compete against an opponent player (e.g. Player A1 with player B1)
3) Depending on the level difference between the players that assigned to compete there is a corresponding score to be achieved (which is provided in a separate table)
For example: Player A1 has a level difference of 0 compared to player B1, which gives us a score of 20.
Player A2 has a level difference of -4 compared to player B2, which gives us a score of 3. etc.
Total score of "My team" in this matchmaking lets say, is 180.
4) Here come the 1 million question:
Is there any way to calculate/define which player combination will give My Team the HIGHEST score?

PS: need to calculate this for bigger teams, like 32 players versus 32 opponent players.

Many thanks in advance for your feedback!

 

[Snakehips]

@vaskas I'm not a mathematician but I reckon that you are going to struggle with this.
With your 5 player example you have 25 possible pairings which yield over 50,000 combinations of 5
Even visually from the below it is not easy to select the highest combination, which I believe is 110 ?

Book2
	L	M	N	O	P	Q	R	S	T	U
1					Lvl	5	2	4	8	6
2				Lvl		Player A1	Player A2	Player A3	Player A4	Player A5
3	LevelDiff	Score		5	Player B1	20	5	15	35	25
4	-4	3		6	Player B2	15	3	10	30	20
5	-3	5		6	Player B3	15	3	10	30	20
6	-2	10		5	Player B4	20	5	15	35	25
7	-1	15		2	Player B5	35	20	30	40	40
8	0	20
9	1	25
10	2	30
11	3	35
12	4	40
13
14
15
Sheet5

Cell Formulas
Range	Formula
Q3:U7	Q3	=LOOKUP(Q$1-$O3,$L$4:$M$12)

With 32 players there are 1024 pairings and a staggering number of possible combinations.

Apologies if I am painting too gloomy a picture.