# Need help for match making formula!

##### New Member
Good evening everyone, hope you are well.

I am trying to figure out a solution for the problem below:

1) assume we have 3 players in our team and 3 opponents in an upcoming tournament lets say.
2) each on of our players can have 1 opponent in a tournament. (no duplication of players or opponents)
3) assume that we know all the scores for all possible combination of players and opponents, i.e.
3.1) if player 1 plays with opponent 1 the score will be 15 for player 1
3.2) if player 1 plays with opponent 2 the score will be 16 for player 1
3.3) if player 1 plays with opponent 3 the score will be 17 for player 1
3.4) if player 2 plays with opponent 1 the score will be 10 for player 2
3.5) if player 2 plays with opponent 2 the score will be 11 for player 2
3.6) if player 2 plays with opponent 3 the score will be 12 for player 2
3.7) if player 3 plays with opponent 1 the score will be 18 for player 3
3.8) if player 3 plays with opponent 2 the score will be 19 for player 3
3.9) if player 3 plays with opponent 3 the score will be 25 for player 3

Question is, is there any way to automatically calculate (or populate the various scenarios) which opponent to assign to our players in order to have the maximum score for the team?

To be more clear, lets have a look at two different tournament scenarios:

Scenario 1:
player 1 plays with opponent 1 the score will be 15 for player 1
player 2 plays with opponent 2 the score will be 11 for player 2
player 3 plays with opponent 3 the score will be 25 for player 3
total team score = 15 + 11 + 20 = 51

Scenario 2:
player 1 plays with opponent 2 the score will be 16 for player 1
player 2 plays with opponent 3 the score will be 12 for player 2
player 3 plays with opponent 1 the score will be 18 for player 3
total team score = 16 + 12 + 18 = 46

As you can see scenario 1 is preferable cause it has more expected score than scenario 2. I need this to be automated in some way.

I also have the data in a table, columns have our player names, rows have opponent names, and respective cells have the score expected for our player.

I would really appreciate your help!

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Consider:

Book3
ABCDEFGHIJKLM
1TableEnemy 1Enemy 2Enemy 3Enemy 4UsThem
2Player 1151617181Player 1Enemy 1
3Player 2101112132Player 2Enemy 2
4Player 3181925283Player 3Enemy 3
5Player 44568
6Player 58477Scenario 1Scenario 2Scenario 3Scenario 4Scenario 5Scenario 6
71-1,2-2,3-31-1,2-3,3-21-2,2-1,3-31-2,2-3,3-11-3,2-2,3-11-3,2-1,3-2
8514651464646
Sheet2
Cell Formulas
RangeFormula
H8:M8H8=INDEX(\$B\$2:\$E\$6,MATCH(INDEX(\$H\$2:\$H\$4,MID(H7,1,1),0),\$A\$2:\$A\$6,0),MATCH(INDEX(\$I\$2:\$I\$4,MID(H7,3,1),0),\$B\$1:\$E\$1,0))+INDEX(\$B\$2:\$E\$6,MATCH(INDEX(\$H\$2:\$H\$4,MID(H7,5,1),0),\$A\$2:\$A\$6,0),MATCH(INDEX(\$I\$2:\$I\$4,MID(H7,7,1),0),\$B\$1:\$E\$1,0))+INDEX(\$B\$2:\$E\$6,MATCH(INDEX(\$H\$2:\$H\$4,MID(H7,9,1),0),\$A\$2:\$A\$6,0),MATCH(INDEX(\$I\$2:\$I\$4,MID(H7,11,1),0),\$B\$1:\$E\$1,0))

Your table is in A1:E6. You put the players that are going to compete in H2:H4, and the opponents in I2:I4. Then there are 6 scenarios to consider (3! = 3*2*1), which are enumerated in H7:M7. The lookup formulas in H8:M8 will tell you the expected results.

Note that this really will only work if you have just 3 players against 3 players. If you go up to 4 playing 4, the number of scenarios jumps to 24, 5 to 120, etc. In that case, you'd want to set it up as a Solver model, or use a VBA macro to create the scenarios.

##### New Member
Welcome to the MrExcel forum!

Consider:

Your table is in A1:E6. You put the players that are going to compete in H2:H4, and the opponents in I2:I4. Then there are 6 scenarios to consider (3! = 3*2*1), which are enumerated in H7:M7. The lookup formulas in H8:M8 will tell you the expected results.

Note that this really will only work if you have just 3 players against 3 players. If you go up to 4 playing 4, the number of scenarios jumps to 24, 5 to 120, etc. In that case, you'd want to set it up as a Solver model, or use a VBA macro to create the scenarios.
Thank you for the prompt reply Mr. Eric!
Actually the matrix I have and i want to look for the possible scenarios is having 32 players against 32 enemies.
But i have no idea how solver of VBA macros
Any guidance for this approach?
Thank you very much!

#### Eric W

##### MrExcel MVP
Just as an FYI, it's usually best to explain what your final requirements are. If you simplify it too much, any suggestions may not work for you, as is the case here.

32 by 32 is a much tougher nut to crack. 32! is about 2.6 X 10^35. That's a 2 followed by 35 zeros! This pretty much puts the problem out of the range of both the Solver, and a typical brute force VBA macro. If a macro could examine a million cases per second, it would still take many times the lifetime of the universe to finish, so that's pretty much out.

There are algorithms that can solve problems like this. They usually involve working with subsets of your cases, then combining them. For example, take 8 sets of 4, figure out the best result for each set. A 4X4 set makes 24 cases, times 8 = 192 total cases. Then, based on the results of that, you rearrange the subsets and try again. You'd repeat until the algorithm tells you it's not going to get any better. Unfortunately, I don't know how to do such an algorithm, nor do I have time to figure one out. You can Google "Knapsack problem" or go to Wikipedia, and check out that algorithm or related ones.

The only other option I can think of is a Monte Carlo type of simulation. This would randomly assign the pairings, figure out the total score, and repeat a thousand times. Keep the best result. Depending on how fast your computer is, you can probably run it more than that. This would not check every case, and statistically it probably would not find the "best" case, but it would probably come close. Let me know if that's of interest.

Replies
1
Views
54
Replies
1
Views
91
Replies
13
Views
465
Replies
3
Views
398
Replies
5
Views
222

1,128,074
Messages
5,628,493
Members
416,321
Latest member
tomazik123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back