Formula to search and select data from multiple cells

**Karenh119** · 02-04-2010, 12:55 PM

When I enter a date into a cell I want a formula that would look thru the 26 different date ranges and select the end date of the previous date range and display that date. Also I’m not sure if I should have the date ranges in a separate worksheet or not.

Example: I enter the date 1/21/09 in cell M4, It would then look thru the date ranges listed below and display the end date of 1/9/09 in cell M8
Begin date End date
12/13/2008 12/26/2008
12/27/2008 1/9/2009
1/10/2009 1/23/2009
1/24/2009 2/6/2009
2/7/2009 2/20/2009
2/21/2009 3/6/2009
3/7/2009 3/20/2009
3/21/2009 4/3/2009
4/4/2009 4/17/2009
4/18/2009 5/1/2009
5/2/2009 5/15/2009
5/16/2009 5/29/2009
5/30/2009 6/12/2009
6/13/2009 6/26/2009
6/27/2009 7/10/2009
7/11/2009 7/24/2009
7/25/2009 8/7/2009
8/8/2009 8/21/2009
8/22/2009 9/4/2009
9/5/2009 9/18/2009
9/19/2009 10/2/2009
10/3/2009 10/16/2009
10/17/2009 10/30/2009
10/31/2009 11/13/2009
11/14/2009 11/27/2009
11/28/2009 12/11/2009
12/12/2009 12/25/2009

**JBeaucaire** · 02-04-2010, 01:15 PM

Begin Date = column A
End Date = column B

Put this formula in M8:

=INDEX($B:$B, MATCH(M4, $A:$A, 1) - 1)

If on a separate page, then like so:

=INDEX(Sheet2!$B:$B, MATCH(M4, Sheet2!$A:$A, 1) - 1)

**Karenh119** · 02-04-2010, 02:09 PM

Thank you so much for correct formula. I have another one that I am trying to figure out.

I would like to pull all the dates between two given dates and list them in separate cells

Example: Date in cell M8 is 1/9/09 and the date in cell N4 is 1/21/09. All the dates in between the two would be listed as follows:
N10 1/10/09
N11 1/11/09
N12 1/12/09
N13 1/13/09
N14 1/14/09
N15 1/15/09
N16 1/16/09
N17 1/17/09
N18 1/18/09
N19 1/18/09
N20 1/19/09

Is it possible for a formula to do this for me?

**JBeaucaire** · 02-04-2010, 02:18 PM

Several ways, but in this instance it's important to see what/where you're doing this. Can you post up a sample workbook showing this scenario including a mockup of the results you desire?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**Karenh119** · 02-04-2010, 02:27 PM

I have manually enter the dates in N10 thru N21 that are the dates between the N4 and M8 dates.

**JBeaucaire** · 02-04-2010, 03:15 PM

Hehe, simpler than I would've come up without seeing the sheet....

In N10:
=M8+1

In N11 and then copied down:
=N10+1

**Karenh119** · 02-04-2010, 03:45 PM

Is there a way to keep it from listing the dates past the date in n4. I would only need the dates listed from the date in M8 up to the date listed in n4.

**JBeaucaire** · 02-04-2010, 04:24 PM

Your layout makes the possibility of any dates past the value in N4 seem unlikely to ever happen. But still, put this alternate formula in N11 and copy down:

=IF(N10+1>$N$4, "", N10+1)