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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!