CONTAINS vs. MATCH - Meeting a formula criteria with multi-select dropdown column
I have a base sheet that collects lessons learned via webform. I want to initiate an approval process based on an approvers list in a separate sheet. I want the Approver column in the base sheet to pull in the names from the Approver sheet if 3 criteria are met:
CSI Division, Type and Topic.
In other words I want 4 people to get notified if the entry contains Division 03, has the continuous improvement type, and contains one of the 3 topics (Design/VDC/Safety)
There are two issues:
- The Index/Match Formula is looking for an exact match rather than containing it. I get a #NO MATCH Error if the "Approval Criteria - Topic" Column has more than one value. I need a way to add IF CONTAINS to the formula below:
=INDEX({Approval Responsibilities - Approver}, MATCH(Type@row, {Approval Responsibilities - Type}, 0))
I tried this one too, but it doesn't work either.
=IF(CONTAINS(Topic@row, {Approval Responsibilities - Topic}), INDEX({Approval Responsibilities - Approver}, MATCH(Topic@row, {Approval Responsibilities - Topic}, 0)))
- Once I figure out the first problem, I need to know how to combine all the criteria formulas into one comprehensive formula in the "Approver" column in the base sheet so that it does this: IF the CSI in the base sheet CONTAINS the CSI in the approval responsibility sheet AND the Type in the base sheet CONTAINS the Type in the approval responsibility sheet AND the Topic in the base sheets CONTAINS the Topic in the approval responsibility sheet THAN populate the Approvers from the Approval Responsibilities sheet into the Approver cell in the base sheet.
Basically I am trying to be able to edit a sheet of approvers and what things they need to approve rather than setting up a million automations.
Answers
-
Unfortunately formulas can't be used to populate multiple useable contacts in a single cell.
The formula you are looking for though (if it was able to actually populate useable contacts) would be an INDEX/COLLECT.
=INDEX(COLLECT({range to pull from}, {criteria range 1}, criteria 1, {criteria range 2}, criteria 2, {criteria range 3}, criteria 3), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!