Puzzling Find If Date Falls Between Two Dates And Return Value

cyndy01

New Member
Joined
Feb 25, 2010
Messages
12
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

MDCurry

Board Regular
Joined
May 3, 2005
Messages
180
Given your spreadsheet described enter the following in cell E1:

Code:
=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

New Member
Joined
Feb 25, 2010
Messages
12
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! ^_^; :p
 

cyndy01

New Member
Joined
Feb 25, 2010
Messages
12
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.
 

cyndy01

New Member
Joined
Feb 25, 2010
Messages
12
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

New Member
Joined
Jun 18, 2013
Messages
1
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

New Member
Joined
Jun 19, 2013
Messages
2
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

New Member
Joined
Jun 19, 2013
Messages
2
But SUMIFS did the trick... :oops: =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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,041
Messages
5,484,333
Members
407,436
Latest member
Szafranski

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top