Find date range

Hi Doctor Excel,
Need help with a formula Please.
I need to figure out the date range of a cell. So if cell "E2" has a date of 11/23/2012 then that would need to fall under date range of "11/19 - 11/25".
I have the following date range I need to identify: "11/12 - 11/18", "11/19 - 11/25", "11/26 - 12/2", "12/3 - 12/9", "12/10 - 12/16", "12/17 - 12/23", "12/24 - 1/1/2013".
I tried the formula below but I keep getting a result of "False". I appreciate any help I can get. Thank you

Answer:

Array formula in cell B2:

=TEXT(INDEX($D$2:$D$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)), "MM/DD")&"-"&TEXT(INDEX($E$2:$E$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)), "MM/DD")

How to create an array formula

1. Select cell B2
2. Click in formula bar
3. Copy (Ctrl + c) and paste above formula (Ctrl + v) to formula bar
4. Press and hold Ctrl + Shit
5. Press Enter

Explaining array formula in cell B2

Step 1 - Find matching date range

IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8),1,0)

returns

{0;1;0;0;0;0;0}

Step 2 - Find row

MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)

becomes

MATCH(1, {0;1;0;0;0;0;0}, 0)

and returns 2.

Step 3 - Return date

INDEX($D$2:$D$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0))

becomes

INDEX({41225; 41232; 41239; 41246; 41253; 41260; 41267}, 2)

and returns 41232.

Step 4 - Convert value to date format

TEXT(INDEX($D$2:$D$8, MATCH(1, IF((A2>=$D$2:$D$8)*(A2<=$E$2:$E$8), 1, 0), 0)), "MM/DD")

 becomes

TEXT(41232, "MM/DD")

and returns 11/19.

Download excel *.xlsx file

Find date range.xlsx