Puzzling Find If Date Falls Between Two Dates And Return Value

[cyndy01]

Hi everyone,

I'm using Excel 2007 and I've tried a lot of different methods including VLookup and Match to solve this problem, but nothing is working.

I have data like this:

A B C
1 12/10/2002 10/9/2003 Period (dec. 10, 2002 - oct. 9, 2003)
2 11/7/2005 3/20/2006 Period (nov. 7, 2005 -mar. 20, 2006)
3 1/2/2007 10/11/2009 Period (jan. 2, 2007 - oct. 11, 2009)

I want to be able to enter a date in another column...say column D and have that date matched up between the dates ranges in a and b then pull the corresponding C value into E . (i.e. 2/10/2008 would put the value Period jan. 2, 2007 - oct. 11, 2009 into E1 and the formula or function could be run down a column.) As you can see, each row has the date ranges and C is the correct period for that range to populate in E.

Vlookup technically works, but I need to be exact in the time periods and Vlookup is a bit scary in that aspect. I need to know for certain that a date falls between this date and this date instead of the approximation.

I used the code Vlookup(d2, $a$1:$c$3, 3, true) and came up with the same answers as if I dropped the second row altogether. If I use false I get the dreaded N/A# error except on dates that exactly match the first date. (i.e. if using the example code if I have a date of 10/9/2003 and false then I would still get an N/A# error even though it is on the same row as 12/10/2002). That second date is as essential for the correct data as the first).

Is there a way to make this happen with either another worksheet function or VBA or have I just run into a wall? Most other solutions I've seen on this board and others don't seem to have all of the elements I need.

I figured with VBA it would be something like If activecell.value>=activesheet.range("a2:b3") then [coding] = "c2..c3...c4...whichever row matches the dates).... but really I'm at a loss. I can't figure out what the coding should be to get the exactness I need.

Can anyone offer any help? I'd be greatly appreciative.

 

[cip888]

Hi MDCurry

This formula is really good, however it does not work when there are duplicate date range, would you be able to adjust the formula in order to allow for duplicate date range?

Thanks a lot

 

[nmprose]

I am brand new to this forum and I just found this solution to a problem that has been making me crazy. However, I have another layer of complexity to add to this formula. I am working in Excel 2016.

I need to look up a date (as the example lists above), but I need to look it up in a named range based on a concatenated city and state and I have multiple named ranges on a worksheet. For example, on sheet 1 within the workbook, there is a drop down for state and a drop down for city - Chicago and Illinois are selected. The formula I am using to call out the name of the range is:

=indirect(concatenate(Sheet1!B6,Sheet1!B7))

which returns "IllinoisChicago". I could change both city and state to Reno, Nevada if I wanted ("NevadaReno"). I need to pull in the time period (column C in the example above) based on the range name such that both Chicago and Reno have the same or similar dates (columns A & B) but a different period listed in Column C.

Any help or suggestions would be greatly appreciated!