Which year in the period

[webforyou]

Hello everyone,
I am facing a question: How can I lookup the value depend on Which year in the period?. I have prepared clean data attached. I use date calculation but it doesn't work.

Could you help me to solve this. Thanks so much in advance.

[Bob Phillips]

I don't see how we test the year from your example.

This gives the 7,050,000 for 21/02/2007

=SUMPRODUCT(--($A$2:$A$31=$G11),--($B$2:$B$31=7),$D$2:$D$31)

but the 7 is hard-coded. Where should it be extracted from.

[webforyou]

I compare the processing date and effective date. To 21/02/2007, the product's year is in between year 6 and 7. So, the year I want is 6, then the value is 7,050,000.

Is this clear enough for you.

Thanks!

[Bob Phillips]

=INDEX($D$2:$D$31,MATCH(1,($A$2:$A$31=G11)*($C$2:$C$31>H11),0)-1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[webforyou]

Many Thanks.

Could you please give me one more instruction about the effective date. If I just have the effective date of first year and the number of year. Can I calculate for the effective date for other middle year like in the excel (I drag cells down to update the value).

Ex:
- The product A has 14 years
- The effective date of first year: 21/03/2000