Index / Match Return Multiple Strings between Two Dates

[artikyulashun]

I have a three columns for 2018 Holidays :

Subject	Start Date	End Date
Weight Loss Awareness Month	1/1/2018	1/31/2018
National Blood Donor Month	1/1/2018	1/31/2018
National Hobby Month	1/1/2018	1/31/2018
Golden Globes	1/7/2018	1/7/2018
Girl Scout Cookie Season Begins	1/1/2018	1/31/2018
Diet Resolution Week	1/1/2018	1/7/2018
Hunt For Happiness	1/4/2018	1/20/2018
New Year's Day	1/1/2018	1/1/2018
Trivia Day	1/4/2018	1/4/2018

<tbody>
</tbody>

I'm trying to use the following formula to return a list of "active" Holidays between two dates.


=IFERROR(INDEX(Holidays,MATCH(1,(start_dates>=T$5)*(end_dates<=X$5),0)),"")


The start date in this example would be 12/27/2018 and the end date would be 1/3/2018.


Appreciate your consideration.

 

[Marcelo Branco]

The start date in this example would be 12/27/2018 and the end date would be 1/3/2018.

Is this a typo? start date greater than end date (?)

M.

 

[artikyulashun]

Yes. Typo. Start date : 12/27/2017; End Date : 1/3/2018

 

[Marcelo Branco]

Considering your data sample above is the expect result just New Year's Day?

M.

 

[artikyulashun]

The List should include everything listed except for the "Golden Globes" and "Hunt for Happiness" which start after 1/3/2018.

Anything that has started, but not ended should appear in the list.

 

[artikyulashun]

The limitation of the formula I started with is that it only pulls holidays that start and end between the two dates entered.

 

[Marcelo Branco]

The List should include everything listed except for the "Golden Globes" and "Hunt for Happiness" which start after 1/3/2018.

Anything that has started, but not ended should appear in the list.

Suppose a holiday that starts at 01/01/2018 and ends at 02/01/2018: should also it be included?

M.

 

[artikyulashun]

That is correct.

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​
1​	Subject​	Start Date​	End Date​		Start​	End​	List​
2​	Weight Loss Awareness Month​	01/01/2018​	01/31/2018​		12/27/2017​	01/03/2018​	Weight Loss Awareness Month​
3​	National Blood Donor Month​	01/01/2018​	01/31/2018​				National Blood Donor Month​
4​	National Hobby Month​	01/01/2018​	01/31/2018​				National Hobby Month​
5​	Golden Globes​	01/07/2018​	01/07/2018​				Girl Scout Cookie Season Begins​
6​	Girl Scout Cookie Season Begins​	01/01/2018​	01/31/2018​				Diet Resolution Week​
7​	Diet Resolution Week​	01/01/2018​	01/07/2018​				New Year's Day​
8​	Hunt For Happiness​	01/04/2018​	01/20/2018​
9​	New Year's Day​	01/01/2018​	01/01/2018​
10​	Trivia Day​	01/04/2018​	01/04/2018​

Array formula in G2 copied down
=IFERROR(INDEX(A$2:A$10,SMALL(IF(B$2:B$10>=E$2,IF(B$2:B$10<=F$2,ROW(A$2:A$10)-ROW(A$2)+1)),ROWS(G$2:G2))),"")
Ctrl+Shift+Enter

M.

 

[artikyulashun]

Works beautifully. Thank you.

However, I'm curious why "Weight Loss Awareness Month" drops when I change the start date to 1/4/2018 and the end date to 1/11/2018. It's still "active" during this time frame.