Need IF Formula involving Dates and Accumulators

[jrosko1]

hi,
I am in need of assistance creating a formula (preferably a formula instead of a macro, if possible, although I am guessing a macro will be necessary). I attached an example.

I am intending for this to be sort of a "calculator" for users. They will input data in B2, B3, B4, and E2. The data in cells A7:A32 will be pre-filled, along with cells B7:B32 (these will be hidden/locked from the user). Then E3 will be calculated primarily based on the date entered in B4.

So I am trying to figure out how to write the formula in E3. For example if the date entered in B4 is between 1/20/2012 and 2/3/2012, then B9 will be subtracted from E2. If the date entered in B4 is between 5/25/2012 and 6/8/2012, then B18 will be subtracted from E2.
The catch is that the dates will change annually, so ideally I would like for the formula to still work on a yearly basis.
Here's the file: Sample.xlsx

[kvsrinivasamurthy]

Pl see attache file.Formula is in E3.

[jrosko1]

Amazing. THANK YOU!

[kvsrinivasamurthy]

There is small problem .I noticed now.

pl change the formula as below


=IF(B4<=A7,E2-B7,IFERROR(E2-INDEX(B7:B32,MATCH(B4,A7:A32,1)+1),"N/A"))

Thanks.

[jrosko1]

thank you for the update. Much appreciated