Lookup a value if the dates fall between two dates

Question

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.

Solution 1

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

=LOOKUP(D1,A1:C10)

see example

Solution 2

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")

Solution 3

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)

Comments

[user3084662]

I have 52 windows...so I would have to do 52 if statements that are nested

[barry houdini]

That's why LOOKUP is good - you can check all 52 windows in one go - see my answer

[Johnny]

this doesn't check if it is in range?

[Johnny]

your lookup doesn't check if it is between two dates.

[Johnny]

Your question could be clearer. Do you just want to look up which windows, across multiple rows that don't exist in your question eg. a1:c52 ? each row being a different window?

[barry houdini]

Assuming no gaps between ranges and ranges in ascending order you don't really need to check within ranges - the Lookup matches D1 with the relevant column A value and returns the correct number from column C. If D1 may not be in any range then you can add a simple IF, like =IF(D1>B10,NA(),LOOKUP(D1,A1:C10))

[Johnny]

if you want to do a lookup do one to find the first row where $Cn==$En.

[barry houdini]

I was waiting for somebody to suggest SUMPRODUCT or SUMIFS, +1 - personally I always prefer to use "lookup" type formulas when a single value is required - they can also be used for retrieving text values...but this approach has advantages, e.g. can have gaps, don't need to be sorted as you say

[barry houdini]

52 windows? Are they weeks?

[CRondao]

Yeh, eventually, if column C is not numeric we add a match to look for value 1 and then use index to get the value. But, in that case, it will be an array formula...