Index / Match Return Multiple Strings between Two Dates

[artikyulashun]

I have a three columns for 2018 Holidays :


[TABLE="width: 500"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Weight Loss Awareness Month[/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[/TR]
[TR]
[TD]National Blood Donor Month[/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[/TR]
[TR]
[TD]National Hobby Month[/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[/TR]
[TR]
[TD]Golden Globes[/TD]
[TD]1/7/2018[/TD]
[TD]1/7/2018[/TD]
[/TR]
[TR]
[TD]Girl Scout Cookie Season Begins[/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[/TR]
[TR]
[TD]Diet Resolution Week[/TD]
[TD]1/1/2018[/TD]
[TD]1/7/2018[/TD]
[/TR]
[TR]
[TD]Hunt For Happiness[/TD]
[TD]1/4/2018[/TD]
[TD]1/20/2018[/TD]
[/TR]
[TR]
[TD]New Year's Day[/TD]
[TD]1/1/2018[/TD]
[TD]1/1/2018[/TD]
[/TR]
[TR]
[TD]Trivia Day[/TD]
[TD]1/4/2018[/TD]
[TD]1/4/2018[/TD]
[/TR]
</tbody>[/TABLE]




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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Subject​

[/td][td]

Start Date​

[/td][td]

End Date​

[/td][td][/td][td]

Start​

[/td][td]

End​

[/td][td]

List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Weight Loss Awareness Month​

[/td][td]

01/01/2018​

[/td][td]

01/31/2018​

[/td][td][/td][td]

12/27/2017​

[/td][td]

01/03/2018​

[/td][td="bgcolor:#D9D9D9"]

Weight Loss Awareness Month​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

National Blood Donor Month​

[/td][td]

01/01/2018​

[/td][td]

01/31/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

National Blood Donor Month​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

National Hobby Month​

[/td][td]

01/01/2018​

[/td][td]

01/31/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

National Hobby Month​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Golden Globes​

[/td][td]

01/07/2018​

[/td][td]

01/07/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Girl Scout Cookie Season Begins​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Girl Scout Cookie Season Begins​

[/td][td]

01/01/2018​

[/td][td]

01/31/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

Diet Resolution Week​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Diet Resolution Week​

[/td][td]

01/01/2018​

[/td][td]

01/07/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"]

New Year's Day​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Hunt For Happiness​

[/td][td]

01/04/2018​

[/td][td]

01/20/2018​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#D9D9D9"][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

New Year's Day​

[/td][td]

01/01/2018​

[/td][td]

01/01/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Trivia Day​

[/td][td]

01/04/2018​

[/td][td]

01/04/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.