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]

Not for me


[TABLE="class: grid"]
<tbody>[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/04/2018
​

[/TD]
[TD]

01/31/2018​

[/TD]
[TD][/TD]
[TD]

12/27/2017​

[/TD]
[TD]

01/03/2018​

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

National Blood Donor 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 Hobby 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"]

Girl Scout Cookie Season Begins​

[/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"]

Diet Resolution Week​

[/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"]

New Year's Day​

[/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"][/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][/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]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[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]

01/04/2018​

[/td][td]

01/11/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"]

Golden Globes​

[/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"]

Girl Scout Cookie Season Begins​

[/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"]

Diet Resolution Week​

[/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"]

Hunt For Happiness​

[/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="bgcolor:#D9D9D9"]

Trivia Day​

[/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="bgcolor:#D9D9D9"][/td][/tr]
[/table]

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.