I have window dates in two cells. For example: a1 = 1/1/2014 8:00 b1 = 1/4/2014 10:00 c1 = 11 Where A is the start date B is the end date C is the code for the window In cell d1 I have the date: 1/3/2014 7:00 I would like to look up the date in a/b and if d falls between those dates then I would like for it to return the code for the window.

Reputation: 33

Lookup a value if the dates fall between two dates

I have window dates in two cells. For example:

Where

In cell d1 I have the date: 1/3/2014 7:00

I would like to look up the date in a/b and if d falls between those dates then I would like for it to return the code for the window.

Upvotes: 3

Reputation: 46331

Assuming you have multiple "windows" listed in order with no gaps perhaps try LOOKUP like

=LOOKUP(D1,A1:C10)

Upvotes: 4

Reputation: 8830

Cell 

a1      = 1/1/2014 8:00

b1      = 1/4/2014 10:00

c1      = 11

d1      = 1/3/2014 7:00

e1      =IF(AND($D$1-A1>0,B1-$D$1>0),C1,"outside")

add add other rows filling down with cells a1:c52

Upvotes: 2

Reputation: 1903

You can use this, it will work even if the dates are not in order, as long as values in C column are numeric. It will return 0 if no match

=SUMPRODUCT((D1>=A1:A10)*(D1<=B1:B10)*C1:C10)

Upvotes: 2