Offset weeks with multiple criteria.

Hi

I'm trying to find a way to look up some weeknumbers based on a set weeknumber and year. My data crosses into the next year and i always need to compare data from 16 weeks in the past with an extra offset of 2 weeks.

In the attached file you'll see a basic setup what i want to achieve. In column A to C there is raw data to work with. Column G and H is where i set the year and week. And in column L to P is where i need to load in the weeks with the extra offset of two weeks. So i have set week 4 and need to get week 2 in column P.

I have did get the normal offset based on criteria to work but as soon as it crosses over into the new year i get negative values instead of week 51. This is basically how for i got.
The names in the formula are different from the example, but i hope you get my point.

=OFFSET(INDEX('Data.xlsb'!GS_WEEK;MATCH(SUMPRODUCT(--('Data.xlsb'!GS_WEEK=$C$4)*(--('Data.xlsb'!GS_YEAR=$C$3))*(--('Data.xlsb'!GS_DAY="Mon"));'Data.xlsb'!GS_WEEK);'Data.xlsb'!GS_WEEK));-14;0)

I hope you can help get my weeks right.
Thanks!

Thanks, but that doesn't work for me because it doesn't the year take into account. Becasue i'm working with year of data this is crucial.

It really needs to be a looked up value from the week column based on what year was set.

If you have more suggestions, i'd love to hear them.

Actually some years like 2015 have 53 weeks. But that actually is quite easily solved...

=IF($C$3=2016,IF($C$4-(16-COLUMNS($C12:C12)+1)<=0,53,0)+$C$4-(16-COLUMNS($C12:C12)+1),IF($C$4-(16-COLUMNS($C12:C12)+1)<=0,52,0)+$C$4-(16-COLUMNS($C12:C12)+1))

Thanks for making clear to me I was overthinking it! And for solving this problem.