Index/Multiple match not linking to correct Start and End weeks

[Ochimus]

As shown on attachment:
Formulae in row 8 needs to show UPLIFT % in PROMO sheet Col F where PROMO Col B=B2 (GROC), PROMO Col E = B5 (25g), and Week number in D3 - O3 is between dates in PROMO Cols C and D
Formulae in row 17 needs to show DISCOUNT value in PROMO Col G when Col B=B2 (GROC), Col E = B5 (25g), and Week number in D3 - O3 is between dates in Cols C and D

As shown on attachment, UPLIFT should be PROMO F6 (5%) for weeks 1 - 4 and PROMO F7 (10%) for weeks 9 - 12, and DISCOUNT should be PROMO G6 (20%) for weeks 1 - 4 and PROMO G7 (15%) for weeks 9 - 12

Tried SUMIFS, IF(AND and INDEX/MATCH approaches, but none work

All solutions, suggestions and alternatives welcome as ever.

Ochimus

[NARAYANK991]

Hi ,

See the attached file.

The formula used in cell D6 in the SUMMARY tab is :

Please Login or Register  to view this content.

The formula used in cell D12 in the SUMMARY tab is :

Please Login or Register  to view this content.

Both the formulas are to be copied across till column O.

Narayan

[Ochimus]

NARAYANK99,

Many thanks for prompt solution. Proves again how valuable this site can be!

Ochimus