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

#### Akkedis

##### New Member
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>

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### steve the fish

##### Well-known Member
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

##### Well-known Member
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

##### New Member
Thanks Steve - that did the trick!
Much appreciated.

Replies
2
Views
265
Replies
6
Views
550

1,109,423
Messages
5,528,681
Members
409,829
Latest member
CFreeamaz

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...