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).

	Games	Games	Games	Goals	Goals	Goals	Assists	Assists	Assists	Shots	Shots	Shots	Dribbles	Dribbles	Dribbles
Date	2004	2005	2006	2004	2005	2006	2004	2005	2006	2004	2005	2006	2004	2005	2006
Ronaldinho	34	30	19	18	12	4	15	11	7	105	96	55	211	187	98
Messi	27	32	37	17	26	44	13	16	9	92	111	194	133	169	192
Eto'o	36	32	25	31	28	19	9	5	7	173	158	118	106	88	56
Ronaldo	28	27	25	27	29	26	6	5	4	166	179	158	152	161	156
Zidane	38	30	28	6	4	8	18	14	10	71	54	49	186	157	134
Figo	37	32	24	14	12	9	12	13	9	85	78	52	169	137	113

<tbody>
</tbody>

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.

Total scouting period (years)	3
	Exercise 1	Exercise 2	Exercise 3	Exercise 4	Exercise 5	Exercise 6
Length of scoutings (as % of scouting period)	14%	21%	29%	7%	14%	14%
Length of scoutings (years)	0.43	0.64	0.86	0.21	0.43	0.43
Scouting end time (years)	0.43	1.07	1.93	2.14	2.57	3.00
Rating occurs in season number	1	2	2	3	3	3
Stamina (Games)			1		1
Finishing (Goals)	1			1
Vision (Assists)		1		1
Shooting (Shots)		1			1
Control (Dribbles)	1

<tbody>
</tbody>

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

Total scouting period (years)	2
	Exercise 1	Exercise 2	Exercise 3	Exercise 4	Exercise 5	Exercise 6
Length of scoutings (as % of scouting period)	14%	21%	29%	7%	14%	14%
Length of scoutings (years)	0.29	0.43	0.57	0.14	0.29	0.29
Scouting end time (years)	0.29	0.71	1.29	1.43	1.71	2.00
Rating occurs in season number	1	1	2	2	2	2
Stamina (Games)			1		1
Finishing (Goals)	1			1
Vision (Assists)		1		1
Shooting (Shots)		1			1
Control (Dribbles)	1

<tbody>
</tbody>

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!