Filter COLLECT(RANGE) by IF Statement

Options

Scenario:

Trying to return a list of active record UIDs based on a common component ID reference value within the row. The following formula is doing 95% of what I need:

=JOIN(COLLECT([Record ID]:[Record ID], [Component ID]:[Component ID], [Lookup ID]@row), CHAR(10))

Related community discussion - thanks to @Shanky Paul for assisting with this: https://community.smartsheet.com/discussion/comment/392701#Comment_392701

Problem:

I'm able to get all the matching row UIDs to return correctly based on the reference, but now can't filter out UIDs for records that are closed.

Approach:

I would normally solve this type of problem with COUNTIFS() or a similar conditional array lookup function, but I don't see that COLLECT has an associated conditional modifier and I'm not just looking for # of instances, but to return text values for any matches.

Because I'm trying to apply the IF condition to the rows in my source RANGE and not on the local lookup / row reference, I'm having a hard time wrapping my head around how to configure this formula. Feels like the answer should look something like this, but this is applying the IF condition to the result of the array and not integral to the array query itself and the syntax is expecting the IF to have an IF TRUE and IF FALSE path, whereas I trying to COLLECT if true and ignore if false.

=JOIN(COLLECT(IF([Record ID]:[Record ID],[Status]@row<>"Closed"), [Component ID]:[Component ID], [Lookup ID]@row), CHAR(10))

Appreciate any assistance in resolving this.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!