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.

Attached Files

DateRangeExample.xlsx (17.1 KB, 14 views)

Maybe like this.

Attached Files

DateRangeExample_2.xlsx (11.6 KB, 14 views)

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

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)