Filter with TextJoin: Multiple Date Ranges in Non-Consecutive Cells

[Pulsar3000]

Hi Everyone:

I'm trying to compare a date to multiple date ranges in non-consecutive cells and if the date falls between the pairs, I want the value preceding the "Start Date" returned.

In the attached example, I want the various colors returned in cell F4 whose date ranges contain the date in cell E4.

I also want the color returned for each occurrence the E4 date was found in the various date ranges. I think the formula in F4 needs the "unique" portion removed.

In the real file I'll be doing this formula in, there will be various dates looked up not just one like in this example but I presume I can just reference the cell with the actual date that needs to be looked up in the formula that I ultimately receive from you.

Look forward to your responses!!

Thank You!

Non-Consecutive Date Ranges.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P
3	Date	Formula
4	3/4/2021	#VALUE!
5
6		Start Date	End Date				Start Date	End Date			Start Date	End Date
7	Blue	1/1/2021	1/7/2021	Something 1	Something 2	Green	3/4/2021	3/15/2021	Something 3	Yellow	3/4/2021	4/30/2021
Sheet2

Cell Formulas
Range	Formula
F4	F4	=TEXTJOIN(", ",TRUE,UNIQUE(FILTER((E7)+(J7)+(N7),(F7,K7,O7>=E4)*((G7,L7,P7)<=E4),"")))

 

[Pulsar3000]

Before dissecting that formula, what about this simpler one instead?

Pulsar3000.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Type	Date	Formula
4	Option 1	8/03/2021	Green, Yellow
5
6
7
8	Type	Item 1	Vague Description 1 Start Date	Vague Description 1 End Date	Something 1	Something 2	Item 2	Vague Description 2 Start Date	Vague Description 2 End Date	Something 3	Item 3	Vague Description 3 Start Date	Vague Description 3 End Date
9	Option 1	Blue	1/01/2021	7/01/2021	Something 1	Something 2	Green	3/02/2021	15/03/2021	Something 3	Yellow	7/03/2021	30/04/2021
10	Option 2	Orange	3/02/2021	17/02/2021	Something 1	Something 2	Pink	18/02/2021	7/03/2021	Something 3	Gray	3/02/2021	23/03/2021
Date Range (2)

Cell Formulas
Range	Formula
F4	F4	=TEXTJOIN(", ",1,IF(RIGHT(F8:O8,6)="t Date",IF(F9:O10<=E4,IF(G9:P10>=E4,IF(D9:D10=D4,E9:N10,""),""),""),""))

Hi @Peter_SSs

Yes, this works! I'm marking your answer as resolved.

 

[Peter_SSs]

Good news! Glad that worked for you.