formula based on region and less than certain values

I have this excel spread sheet that I need to create a formula for - I can do the formula in all ifs but it will be unmanageable I believe. What I need to do is match the C column (Zone) in the 'Week 1' sheet with the Zone column in the 'Rates' sheet. Then I need to test the value of the weight column in 'Week 1' against columns B:F in the 'Rates' sheet. if it is less than the value in the B column then choose that exact value (Minimum Value). If it is less then the C multiply weight (in Week 1) * rate (in Zone). Same with C column, D column, and E column. then F it it falls less than that value choose just that number again (Maximum Value) but if it is over the weight in the F column then I need N/A or something to that effect. *Each zone may have different weight for each column

Here is the file for you to understand better.APRIL 2012.xlsx