Tough excel problem: Lookup Array and use sumproduct on it

[Nirmolak]

I am trying to lookup a column array based on a certain criteria and then sumproduct the values in that column array with another set of values for the answer.

In the sheet, there are certain players who are being scouted. Their stats (games, goals, assists, etc.) have been presented by season (2004-2006).
[TABLE="width: 1206"]
<tbody>[TR]
[TD][/TD]
[TD]Games[/TD]
[TD]Games[/TD]
[TD]Games[/TD]
[TD]Goals[/TD]
[TD]Goals[/TD]
[TD]Goals[/TD]
[TD]Assists[/TD]
[TD]Assists[/TD]
[TD]Assists[/TD]
[TD]Shots[/TD]
[TD]Shots[/TD]
[TD]Shots[/TD]
[TD]Dribbles[/TD]
[TD]Dribbles[/TD]
[TD]Dribbles[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[/TR]
[TR]
[TD]Ronaldinho[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]187[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]Messi[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]194[/TD]
[TD="align: right"]133[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]192[/TD]
[/TR]
[TR]
[TD]Eto'o[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]173[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]Ronaldo[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]Zidane[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]186[/TD]
[TD="align: right"]157[/TD]
[TD="align: right"]134[/TD]
[/TR]
[TR]
[TD]Figo[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]137[/TD]
[TD="align: right"]113[/TD]
[/TR]
</tbody>[/TABLE]

The scouting team runs a fixed set of exercises (judging different aspects) to scout the players. The length of each scouting exercise is tied to the length of the total scouting period (row 1), e.g. exercise 1 will be completed in ~14% of the total scouting period's time (0.43 years if scouting period is 3 years). The exercise rating is submitted immediately on completion of an exercise and the "rating occurs in season" row depicts the year no. when the ratings are submitted. The 1 is just to see the attributes being scouted.
[TABLE="width: 630"]
<tbody>[TR]
[TD]Total scouting period (years)[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Exercise 1[/TD]
[TD]Exercise 2[/TD]
[TD]Exercise 3[/TD]
[TD]Exercise 4[/TD]
[TD]Exercise 5[/TD]
[TD]Exercise 6[/TD]
[/TR]
[TR]
[TD]Length of scoutings (as % of scouting period)[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]14%[/TD]
[/TR]
[TR]
[TD]Length of scoutings (years)[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.64[/TD]
[TD="align: right"]0.86[/TD]
[TD="align: right"]0.21[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.43[/TD]
[/TR]
[TR]
[TD]Scouting end time (years)[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]1.07[/TD]
[TD="align: right"]1.93[/TD]
[TD="align: right"]2.14[/TD]
[TD="align: right"]2.57[/TD]
[TD="align: right"]3.00[/TD]
[/TR]
[TR]
[TD]Rating occurs in season number[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Stamina (Games)[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finishing (Goals)[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vision (Assists)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shooting (Shots)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Control (Dribbles)[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In the output sheet, I need to show the scout rating for a given season number, e.g. in season 1, the rating submitted for Ronaldinho will be = goals (18*1) + dribbles(211*1) = 219
the formula i am using for this is: SUMPRODUCT(CHOOSE({1;2;3},AY4,CI4,DS4),Ratings!$B$46:$B$49)
but if i change the scouting period to 2 years (below), the rating for ronaldinho becomes: Exercise1(18*1 + 211*1) + exercise2(15*1 + 105*1). I want to build a formula that can dynamically capture the columns which correspond to season 1 once the scouting period changes

[TABLE="width: 630"]
<tbody>[TR]
[TD]Total scouting period (years)[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Exercise 1[/TD]
[TD]Exercise 2[/TD]
[TD]Exercise 3[/TD]
[TD]Exercise 4[/TD]
[TD]Exercise 5[/TD]
[TD]Exercise 6[/TD]
[/TR]
[TR]
[TD]Length of scoutings (as % of scouting period)[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]14%[/TD]
[/TR]
[TR]
[TD]Length of scoutings (years)[/TD]
[TD="align: right"]0.29[/TD]
[TD="align: right"]0.43[/TD]
[TD="align: right"]0.57[/TD]
[TD="align: right"]0.14[/TD]
[TD="align: right"]0.29[/TD]
[TD="align: right"]0.29[/TD]
[/TR]
[TR]
[TD]Scouting end time (years)[/TD]
[TD="align: right"]0.29[/TD]
[TD="align: right"]0.71[/TD]
[TD="align: right"]1.29[/TD]
[TD="align: right"]1.43[/TD]
[TD="align: right"]1.71[/TD]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD]Rating occurs in season number[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Stamina (Games)[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finishing (Goals)[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Vision (Assists)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shooting (Shots)[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Control (Dribbles)[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Whats important is that the year (2004/05/06) for which the player stats are pulled corresponds to the season number (1/2/3).

If someone could help me find a formula for this that i can apply to a wider dataset as well, i would really appreciate it. i do not want to change the format of the data since the wider dataset is much larger than this.

Thanks!