#1
|
|||
|
|||
Find a Date in a Range
The attached spreadsheet contains a range of beginning and ending dates along with some other data in A2 thru D13. In column E2 there is a date value. I need a formula that will search for which date range the date in E2 is in and find the group leader for that date range in F2. Note, the data itself for this is just a made up sample made up in order to create the formula. My real application is much more complex. |
#2
|
|||
|
|||
Find a Date in a Range
Maybe like this.
|
#3
|
|||
|
|||
Please forget my previous post.
Use the following array formula instead: =INDEX(D213,MATCH(1,((A2:A13>=DATE(YEAR(E2),MONT H(E2),1))*(B2:B13<=DATE(YEAR(E2),MONTH(E2)+1,0))), 0)) (Must be entered by holding down Ctrl and Shift before pressing Enter). (Anyone who knows how to avoid the Big Grin icon when one enters for example D2 colon D13?) |
#4
|
|||
|
|||
Your formula did not work for me, but I got the following formula to work.
=LOOKUP(2,1/((E2>=$A$2:$A$13)*(E2<=$B$2:$B$13)),$D$2:$D$13) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Check if a Date falls within a date range | OTPM | Excel | 7 | 02-03-2016 09:11 PM |
MS Project Date Range Issue | appelguy@gmail.com | Project | 2 | 10-31-2015 11:52 AM |
find IP in range / find number between numbers | gn28 | Excel | 4 | 06-14-2015 03:46 PM |
Restrict Date Range Calculations | akatz85 | Excel | 4 | 03-27-2014 08:46 PM |
Date Range Prompt | MikeWooZ | Excel | 6 | 12-04-2011 02:10 PM |