SUMPRODUCT function using columns that match criteria across different sheets

[DotAGenius]

Hi all!

First time posting here.

I am trying to produce a spreadsheet to see an array of "Weighted Scores" based on different criteria.
If anybody plays FIFA, this might be easier to understand.

Players have "Attributes" like Shooting, Passing, Defending, and Dribbling.
You can assign "Styles" to players that give bonuses like +15 to Shooting, +20 to Defending, or +10 to both Shooting and Passing, etc.
I also created "Archetypes," such as a "Wing Player" who would benefit from having good Shooting and Passing, but not so much from Defending.

On the first sheet, I have a list of Players and all their Attributes. For example, it would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Player A[/TD]
[TD]Player B[/TD]
[TD]Player C[/TD]
[TD]Player D[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]80[/TD]
[TD]70[/TD]
[TD]90[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]70[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

On the second sheet, I list out what the Styles do. For example, it would look like this, to indicate what bonuses each Style gives (e.g. applying Style Z to a Player would give them +20 to Dribbling):
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Style W[/TD]
[TD]Style X[/TD]
[TD]Style Y[/TD]
[TD]Style Z[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


On the third sheet, I list out the Archetypes and how important different Attributes are for each respective Archetype (out of 1.00). For example, it would look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Archetype 1[/TD]
[TD]Archetype 2[/TD]
[TD]Archetype 3[/TD]
[TD]Archetype 4[/TD]
[/TR]
[TR]
[TD]Shooting[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Passing[/TD]
[TD]0.5[/TD]
[TD]0.65[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Defending[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]Dribbling[/TD]
[TD]0[/TD]
[TD]0.35[/TD]
[TD]0.5[/TD]
[TD]0.2[/TD]
[/TR]
</tbody>[/TABLE]

Phew, ok! So now that all that's out of the way, this is what I hope to accomplish.

I want to be able to quickly determine the best Style/Archetype combination for every Player based on Weighted Scores for each combination. So far, I have been manually selecting each Archetype and each Style and comparing them using drop-down lists and SUMPRODUCT. For example, Player A with Style W equipped and playing as Archetype 1 would be the SUMPRODUCT of (the Array under Player A + Array under Style W (to get total Attributes)) * (the Array under Archetype 1) to get one Weighted Score. In this case, it would be [95,80,20,45] * [0.5,0.5,0,0] to get a Weighted Score of 241.

On my fourth sheet, this is what I have (with A1 being a drop-down list of all Players):
[TABLE="width: 500"]
<tbody>[TR]
[TD]Player 1[/TD]
[TD]Archetype 1[/TD]
[TD]Archetype 2[/TD]
[TD]Archetype 3[/TD]
[TD]Archetype 4[/TD]
[/TR]
[TR]
[TD]Style W[/TD]
[TD]241[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Style Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to see the Weighted Score per each Style/Archetype combination, so I can quickly determine how this Player is best utilized.
The problem I am facing is finding a way to locate the Attributes for the Player selected from the list in A1, and then using that as the first part of the first Array.
I tried to do some sort of SUMPRODUCT function with MATCH, but I couldn't figure out how to do it in a robust manner.

If anybody has a solution, I would be extremely grateful!

Thank you in advance!

 

[DotAGenius]

Genius!

Thanks Marcelo.

Quick question, does it have to be an array formula because it checks each individual entry in the array?

 

[Marcelo Branco]

Must be an array formula because of this condition
IF (Array1+Array2>99,99,Array1+Array2)
that is
IF({105;80;20;45}>99,99,{105;80;20;45}) results in {99;80;20;45}

M.

 

[DotAGenius]

Awesome, thanks! Learned a lot today