How to lookup a date in a range and return a value if found

[Akkedis]

Hello experts!
How can I find a value in a list that is dependent on a entered date being found in a date range?
Eg.
I have a list of materials with valid-from and valid-to dates, and prices for those materials (in A1:D9)
Now I enter a order date (B12), and a list of materials (A14:A16).
Question: How do I find the correct price per material (B14:B16) based on my Order Date?
See sheet below:

	A	B	C	D	E
1	Material	Valid from	Valid to	Price
2	Pen	25.10.2013	31.12.2013	95.00
3	Pen	01.01.2014	24.10.2014	150.00
4	Pen	25.10.2014	31.12.2014	100.00
5	Brush	25.10.2013	10.03.2014	200.00
6	Brush	11.03.2014	31.12.2014	205.00
7	Ruler	25.10.2013	31.12.2013	99.00
8	Ruler	01.01.2014	24.10.2014	103.00
9	Ruler	25.10.2014	31.12.2014	111.00
10
11
12	Order Date:	15.10.2014
13	Material	Price
14	Pen	?
15	Brush	?
16	Ruler	?

<tbody>
</tbody>

Thanks in advance!

 

[steve the fish]

Hi

Try:

=SUMIFS($D$2:$D$9,$A$2:$A$9,A14,$B$2:$B$9,"<"&$B$12,$C$2:$C$9,">"&$B$12)

 

[steve the fish]

Actually should be this to be inclusive:

=SUMIFS($D$2:$D$9,$A$2:$A$9,A14,$B$2:$B$9,"<="&$B$12,$C$2:$C$9,">="&$B$12)

 

[Akkedis]

Thanks Steve - that did the trick!
Much appreciated.