help need with multi select lookup: JOIN/COLLECT/HAS?

I am trying to look up the email addresses for employees where multiple employees are in a single cell. I use index matching for single employee names successfully, but am struggling with a multi select.

The employee column (Who are you appreciating?) is multi select. The standard index match processes a "#NO MATCH" when more than one name is in the column. My "try" using JOIN/COLLECT/HAS works with a single name, but returns a blank with multiple names.

=JOIN(COLLECT({Employee List Email}, {Employee List Name}, HAS(@cell, [Who are you appreciating?]@row)), ";")

I have a straight forward employee look up sheet.

Any advice is appreciated!

Katharine

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!