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.

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.

Sam Swain

Amazing thanks for your help again Paul! I'll figure out the rest

Paul Newcome

Happy to help. 👍️