Pulsar3000
New Member
- Joined
- Apr 19, 2021
- Messages
- 44
- Office Version
- 365
- Platform
- Windows
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!
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),""))) |