Using CONTAINS with JOIN(COLLECT)
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
-
Hi @scisar,
Your CONTAINS section of the formula is incorrect, I think. It should be:
...CONTAINS([Priority Name]@row, @cell)...
Try substituting that in and seeing it if solves your issue.
Answers
-
Hi @scisar,
Your CONTAINS section of the formula is incorrect, I think. It should be:
...CONTAINS([Priority Name]@row, @cell)...
Try substituting that in and seeing it if solves your issue.
-
Hi @Nick Korna,
This worked! Thanks so much. I don't really understand the @cell reference, though. Can you help me out with why that fixed the problem?
Thanks,
Sara
-
Have a look at these:
These should hopefully give you some pointers and examples. 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!