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]

Not for me

	A​	B​	C​	D​	E​	F​	G​
1​	Subject​	Start Date​	End Date​		Start​	End​	List​
2​	Weight Loss Awareness Month​	01/04/2018 ​	01/31/2018​		12/27/2017​	01/03/2018​	National Blood Donor Month​
3​	National Blood Donor Month​	01/01/2018​	01/31/2018​				National Hobby Month​
4​	National Hobby Month​	01/01/2018​	01/31/2018​				Girl Scout Cookie Season Begins​
5​	Golden Globes​	01/07/2018​	01/07/2018​				Diet Resolution Week​
6​	Girl Scout Cookie Season Begins​	01/01/2018​	01/31/2018​				New Year's Day​
7​	Diet Resolution Week​	01/01/2018​	01/07/2018​
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​
11​

<tbody>
</tbody>

Have you confirmed the formula with Ctrl+Shift+Enter, not just Enter?

M.

 

[artikyulashun]

Yes. Ctrl+Shift+Enter.

Apologies. I meant changing the start and end dates in Columns E and F.

Column B should still say 1/1/2018.

 

[Marcelo Branco]

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.

hmm...i think i misunderstood what you are looking for

See if this does what you need

	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​		01/04/2018​	01/11/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​				Golden Globes​
6​	Girl Scout Cookie Season Begins​	01/01/2018​	01/31/2018​				Girl Scout Cookie Season Begins​
7​	Diet Resolution Week​	01/01/2018​	01/07/2018​				Diet Resolution Week​
8​	Hunt For Happiness​	01/04/2018​	01/20/2018​				Hunt For Happiness​
9​	New Year's Day​	01/01/2018​	01/01/2018​				Trivia Day​
10​	Trivia Day​	01/04/2018​	01/04/2018​

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

M.