INDEX COLLECT Multiple Values that match a date range

Sam Swain

Hello community!

I want to return the name of every BROADCAST that falls within the date range where I have a contractor coming on site at my office.

In my contractor sheet I have a start and finish date column.

Contractor Start: 02/20/24 Contractor Finish: 02/21/24

In my Broadcast sheet, I have over 100 broadcasts spanning over the year.

My first formula in my contractor sheet tells me how many broadcasts clash with my contractor date range - this is working perfectly.

=COUNTIFS({Start}, [Start Date]@row >= @cell, {Finish}, [Finish Date]@row <= @cell, {B/M.}, "BROADCAST")

The value returned is 3. As there are 3 values (3 broadcasts) that clash, I want to somehow return the name of all 3 of these broadcast values. Here is the formula I have:

=INDEX(COLLECT({Broadcast}, {Start}, [Start Date]@row >= @cell, {Finish}, [Finish Date]@row <= @cell), 1)

I need to add an if statement to this formula so that it only reads the dates that are broadcast items (I have a helper column for this I just need to know how to utilise it within an index collect formula).

Then I need it to return all 3 values in a list.

Does anyone know if this is possible?

Paul Newcome

The first thing to do is to add another range/criteria set to evaluate the helper column that indicates whether or not it is a broadcast so that you can further filter by broadcast vs not.

Then you need to switch from an INDEX to a JOIN. INDEX will only ever pull one instance. The JOIN will display multiple instances.