INDEX COLLECT Multiple Values that match a date range

Sam Swain
Sam Swain ✭✭✭
edited 02/15/24 in Formulas and Functions

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?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!