return value if date is between two other dates, depending on the year of another cell

[wolthers]

Hey guys, I have a huge spreadsheet with data since 2010 and I want to see prices per period, depending on year and for when the goods were sold. I have two columns with date, one is the day of trade(column B) and the other is day the goods will be delivered (column C), then I have a third column with price (D) - next two columns are where the formulas will go, Column (E) will be for nearby delivery and column (D) will be for future deliveries. I got another spreadsheet with defined names so I won't get lost too easy, these defined names are basically the start month and end month to determine the nearby shipment and future shipment.

Here is what my defined names look like to determine the deliveries for nearby & future
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]cellname[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]cellname[/TD]
[/TR]
[TR]
[TD]start10[/TD]
[TD]July, 1, 2010[/TD]
[TD]June, 30, 2011[/TD]
[TD]end10[/TD]
[/TR]
[TR]
[TD]start11[/TD]
[TD]July, 1, 2011[/TD]
[TD]June, 30, 2012[/TD]
[TD]end11[/TD]
[/TR]
[TR]
[TD]start12[/TD]
[TD]July, 1, 2012[/TD]
[TD]June, 30, 2013[/TD]
[TD]end12[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Contract nr[/TD]
[TD]Day of trade[/TD]
[TD]delivery month&year[/TD]
[TD]Nearby delivery[/TD]
[TD]Future delivery[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD]January, 5, 2010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Feb,2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]formula1[/TD]
[TD]formula2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]January, 7, 2010[/TD]
[TD]Mar, 2010[/TD]
[TD]formula1[/TD]
[TD]formula2[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]


I have the following formula1 so far:

[FONT=Lucida Grande]=IF(YEAR[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande][@Closing][/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]=2010,IF[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]AND[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande]C2[/FONT][/COLOR][FONT=Lucida Grande]>=start10,[/FONT][COLOR=#006107][FONT=Lucida Grande]C2[/FONT][/COLOR][FONT=Lucida Grande]<=finish10[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande],[/FONT][COLOR=#AB30D6][FONT=Lucida Grande][@Diff][/FONT][/COLOR][FONT=Lucida Grande], NA[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]()[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

and formula2 so far:

[FONT=Lucida Grande]=IF(YEAR[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande][@Closing][/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]=2010,IF[/FONT][COLOR=#006107][FONT=Lucida Grande](C2[/FONT][/COLOR][FONT=Lucida Grande]>finish10,[/FONT][COLOR=#AB30D6][FONT=Lucida Grande][@Diff][/FONT][/COLOR][FONT=Lucida Grande], NA[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]()[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

I can't seem to figure out a way to do this code work for my whole spreadsheet, I need something like this:

If(YEAR(Day of trade cell) is between start10 and finish10 AND (delivery month&year) is also between start10 and finish10, show price) IF (YEAR(Day of trade cell) is between start11 and finish11 AND (delivery month&year) is also between start11 and finish11, show price)) this repeated until 2016 // there are about 11k lines on this, which is why I wanted this formula to auto-determine.

My problem that I can't figure out how to make formulas 1 and 2:

1. If day of trade is for instance October 2010, it has to see if the delivery date is between July 2010 and June 2011, if positive then value is price cell, if not, do nothing. Or if day of trade is October 2011, it has to see if the delivery date is between the same period, and so on...
2. If day of trade is for instance October 2010, it has to see if the delivery date is bigger than July 2011, if positive then the value is price of cell, if not, do nothing.

I am not sure if I was able to explain myself well, but I did my best hehehe - I might tend to overcomplicate things