Using CONTAINS with JOIN(COLLECT)

Options
scisar
scisar ✭✭✭
edited 07/12/23 in Formulas and Functions

Hello!

I'm trying to add CONTAINS to an existing JOIN(COLLECT) formula, and I can't seem to get it to work.

The existing (working) formula is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, "1", {RAID Log Category}, "Issue", {RAID Log Top 10 Priority}, [Priority Name]@row), ", ").

The formula I've come up with to add CONTAINS is: =JOIN(COLLECT({RAID Log Title}, {RAID Log Flag}, "1", {RAID Log Category}, "Issue", {RAID Log Top 10 Priority}, CONTAINS([Priority Name]@row, {RAID Log Top 10 Priority})), ", ").

The problem with the CONTAINS formula above is that it doesn't return any results in any of the cells the formula is applied to.

The reason I want to move from the 1st formula to the 2nd is that the cross-referenced sheet now possibly lists more than 1 RAID Log Top 10 Priority in that column, and I want the text of the RAID Log Title to be returned when the Priority Name of the row can be found in the RAID Log Top 10 Priority (not just when it's an exact 1:1 match).


Any help would be much appreciated.

Thanks!

Sara

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!