returning multiple cells corresponding to single date range

[excellerator13]

Hello,

In column a of my sheet I have a start date. In colum b, I have an end date. Columns c through e contain information corresponding to the date range in column a and b. I would like to be able to enter a date on a separate sheet and do the following:

(1) determine what date ranges from columns a and b the specific date falls between.

(2) If the entered date falls between one or several of the ranges, then I would like to return on the separate sheet the entire row/rows that correspond to that date range.

Because each date will match multiple ranges, I am not sure that an if function will work. Thanks in advance!

 

[Akashwani]

Hi and welcome to MrExcel.

Do you mean something like this?....

Sample Data....

Excel Workbook
	A	B	C	D	E	F
1	Start Date	End Date	Data1	Data2	Data3
2	01/01/2013	08/01/2013	Bart Simpson	cat	Bike
3	02/01/2013	09/01/2013	Lisa Simpson	dog	Helicopter
4	03/01/2013	10/01/2013	Grandpa Simpson	Horse	Bus
5	04/01/2013	11/01/2013	Lisa Simpson	Mouse	train
6	05/01/2013	12/01/2013	Marge Simpson	fish	Helicopter
7	06/01/2013	13/01/2013	Maggie Simpson	cat	Plane
8	07/01/2013	14/01/2013	Milhouse Van Houten	Panda	Tut Tut
9	08/01/2013	15/01/2013	Ned Flanders	Tiger	Coach
10	09/01/2013	16/01/2013	Otto Mann	Lion	Carriage
11	10/01/2013	17/01/2013	Rod Flanders	Tiger	Bus
12	11/01/2013	18/01/2013	Tod Flanders	cow	trike
13	13/01/2013	20/01/2013	Moe Szyslak	dog	Car
14
Sheet1

Example Results.....

Excel Workbook
	A	B	C	D	E
1	Date	Data1	Data2	Data3
2	08/01/2013	Bart Simpson	cat	Bike
3		Ned Flanders	Tiger	Coach
4
Sheet2

The formula in B2 needs to be entered with ctrl shift enter NOT just enter, it can then be copied across and down.
You will obviously need to change the cell references and sheet name to suit your layout.

I hope that helps.
If, not, then you need to post some sample data and your expected results, you can post sample data using the link in my signature.
You can also find some very helpful formulas to "Extract" multiple records here......

Search/Lookup | Get Digital Help

And some excellent videos here....

excelisfun -- Excel How To Videos - YouTube

Ak

 

[excellerator13]

Wow! Thanks so much for the quick response; this was very helpful. The date that will be entered will not always match the start or end date in sheet 1. In your example, for instance, I would like to be able to enter 5/11/2013 and have it return the data next to all of the date ranges that 5/11/2013 falls between. In your example, this would be the data for the A2:B6 ranges. Any ideas on how I can capture dates within the range? Thanks again!

 

[Akashwani]

Hi,

Is this what you mean?...

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Start Date	End Date	Data1	Data2	Data3
2	08/01/2013	01/01/2013	08/01/2013	Bart Simpson	cat	Bike
3		02/01/2013	09/01/2013	Lisa Simpson	dog	Helicopter
4		03/01/2013	10/01/2013	Grandpa Simpson	Horse	Bus
5		04/01/2013	11/01/2013	Lisa Simpson	Mouse	train
6		05/01/2013	12/01/2013	Marge Simpson	fish	Helicopter
7		06/01/2013	13/01/2013	Maggie Simpson	cat	Plane
8		07/01/2013	14/01/2013	Milhouse Van Houten	Panda	Tut Tut
9		08/01/2013	15/01/2013	Ned Flanders	Tiger	Coach
10
Sheet2

The formula in B2 needs entering with ctrl shift enter NOT just enter.

Take a look here....

Excel Magic Trick 894: Extract Records Between Two Dates: Filter or Formula - YouTube

I hope this helps.

Ak

 

[excellerator13]

That worked, thanks! If the cell in the original sheet is hyperlinked, is there any way to maintain the hyperlink when it is copied to the second sheet? I tried hyperlinking via the =Hyperlink function, but that did not work.

 

[Akashwani]

Hi,

I honestly have no ideas on this, other than try.......

=HYPERLINK(IF(ROWS(B$2:B2)<=SUMPRODUCT(--(Sheet1!$A$2:$A$13<=$A$2)*(Sheet1!$B$2:$B$13>=$A$2)),INDEX(Sheet1!A$2:A$13,SMALL(IF((Sheet1!$A$2:$A$13<=$A$2)*(Sheet1!$B$2:$B$13>=$A$2),ROW(Sheet1!A$2:A$13)),ROWS(B$2:B2))-ROW(Sheet1!$A$2)+1),""))

Formula needs to be entered with ctrl shift enter NOT just enter.

I hope that helps.

Ak