How to pull multiple values based on date ranges

Question

I have this list of events (A) that has a start (B) and end date (C). Now what I try to achieve is to create a new list of days (E), like a calendar, that collects all events taking place that day (F).

Meaning if Event A is on Jan 1 - 3. And Event B is on Jan 2 - 4. It should look like this:

1. jan: Event A
2. jan: Event A + Event B
3. jan: Event A + Event B
4. jan: Event B

I have tried FILTER, VLOOKUP, IF and IFERROR without any luck simply due to the fact that I stuggle with the formula embracing the "in between dates" aspect.

I have succeeded using both filters and Vlookup to sort out everything happening based on one of the values B or C, but not the >=B AND <=C combined.

For that, what does work is simply saying =IF(AND(E3>=B3,E3<=C3),A3,"") It would successfully add the first event. And I could copy this code onto G,H,I.. horizontally to check for A4,A5,A6,A7 etc. The problem is, when that list of events is 500 rows long, it just makes no sense to set up those 500 cells of code individually - when there should be an easier way to have this automated as an array?

Clearly I must be missing just that little extra code to have this work. Hoping anyone in here are able to see the details in pulling this off?

For the record, I am prepared to get to the final result in at least two steps. First pull each event into individual cells, and then for a second table concatenate that info into the final one cell with TEXTJOIN. (=TEXTJOIN(" + ",TRUE,F3:Z3,))

Answer 1

Welcome!

You are absolutely right - the FILTER() function solves this problem. The main difficulty is to formulate a double condition. This is where the trick comes in, just multiplying booleans (and the result of comparing dates is a boolean) is equivalent to the AND() function. So your formula could be:

=TEXTJOIN(" + ",1,FILTER($A$3:$A,($B$3:$B<=$E3)*($C$3:$C>=$E3)))

Please, next time, instead of the image of the table, provide a link to the real table with test data, this will allow us to save our time, and you will get an answer faster