Help! Multiselect Column Formula with INDEX, MATCH and HAS


Hello Smartsheet World!

I am attempting to run a formula that will return contact emails depending on which departments are selected in a multi-select dropdown column.

Sheet 1: Improvement Notes Smartsheet

-Dept Attention column is a multi-select dropdown

-Department Contacts column (in green) is a contact list that allows multiple contacts (this is where I want to add a formula that auto-populates the contacts based on the departments selected in the Dept Attention column)

Sheet 2: Department Contact Guide Smartsheet

- Smartsheet that the formula would reference to lookup which contacts are associated with each department

From my research, I've seen a combination of formulas but am stumped on what direction to take:

=IF(NOT(ISBLANK([Department Attention@row])), INDEX(COLLECT({Department Contact Guide Contacts }, {Department Guide Dept Attention}, HAS(@cell, [Department Attention@row]), 1), " ")

The formula needs to be fixed and I'm unsure if I'm doing this right or if what I'm attempting to accomplish is possible.

Any guidance is greatly appreciated!


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!