CONTAINS vs. MATCH - Meeting a formula criteria with multi-select dropdown column

Jared Davis

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.

Paul Newcome

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)