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.

 

[MDCurry]

Given your spreadsheet described enter the following in cell E1:

=IF(ISERROR(INDEX(C:C,MATCH(SUMPRODUCT((A:A<=D1)*(B:B>=D1)*(A:A)),A:A,0))),"Not Found",INDEX(C:C,MATCH(SUMPRODUCT((A:A<=D1)*(B:B>=D1)*(A:A)),A:A,0)))

 

[cyndy01]

Wow! This worked great! I never really understood how sumproduct worked. I still don't even after looking at this...I'm probably actually more confused but I don't care because it works! ^_^ You're the best MD thank you!

Now if only I could find an non-confusing tutorial on how to use the sumproduct function...I would complete my goal of...learning how to use sumproduct. My goals are small...I know...but one step at a time and all that! ^_^;

 

[MDCurry]

Here's a very good source in my opinion.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[cyndy01]

Thanks so much again, MD! This helped me learn enough about SUMPRODUCT to impress my boss! He wasn't familiar with it and now we have a great new tool! You're my hero! ^_^

I would like to offer some advice to anyone else planning on using this as well.

SUMPRODUCT is super slow if you use an entire column. Limit your range to speed up the calculations. So instead of using A:A set your range to just what you have populated. If your data is in A2:A8 just go $A$2:$A$8. I've seen lots of posts elsewhere regarding how slow SUMPRODUCT is but tweaking it you gain speed and really...you gotta love pulling in data from closed worksheets.

 

[mcmurry]

I had a 3 way lookup issue and found the solution here.
It also has the benefit of not requiring concatenation (tricky with dates) and not involving sumproduct (which is slow).

Excel Magic Trick #225: 2 Way Lookup With Duplicates
http://www.youtube.com/user/ExcelIsFun#p/search/0/xKIVPN_U5hA

 

[cyndy01]

Wow, MD! You really came through with this one! I added that to my favorites! The SUMPRODUCT was good but this is even better! You're definitely in the know and so am I now. ^_^

 

[rloots]

Good day,

I have a similar question with regards to getting a value returned that falls within 2 dates.

Sheet 1
Employee Start Date End Date Work Schedule
100 10/12/2000 31/12/2000 Needs to return Schedule A from sheet 2
100 01/12/2003 28/02/2006 Needs to return Schedule B form sheet 2


Sheet 2
Employee Start Date End Date Work Schedule
100 01/12/2000 10/01/2003 Schedule A
100 01/11/2003 28/02/2006 Schedule b
It's important that the employee number is checked as this sheet contain multiple employees records. You help is much appreciated

 

[ragman]

Hi

I too have a similar question.

Sheet1
Date Employee1 Employee2
1 Jan 2013 Code1 Code2
2 Jan 2013 Code4 Code2

Sheet2
Employee Start Date End date Code
Employee1 1 Jan 2013 1 Jan 2013 Code1
Employee2 1 Jan 2013 2 Jan 2013 Code2

I need help to handle duplicates. This one works as long as different employees don't have matching dates.

=IFERROR(INDEX(Sheet2!$D$2:$D$1000,MATCH($B$1&SUMPRODUCT((Sheet2!$B$2:$B$1000<=$A2)*(Sheet2!$C$2:$C$1000>=$A2)*(Sheet2!$B$2:$B$1000)),Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$1000,0)),"")

 

[ragman]

But SUMIFS did the trick... =SUMIFS(Sheet2!$D$2:$D$18;Sheet2!$A$2:$A$18;Sheet1!B$1;Sheet2!$B$2:$B$18;"<="&Sheet1!$A2;Sheet2!$C$2:$C$18;">="&Sheet1!$A2)