Multiselect column for each row return all associated matches from another sheet.

Stevey C

I have a convoluted problem, index match only works for single input, and I have a multiple select column for business unit with up to 6 options resulting in up to 6 individuals needing to populate the contact column from sheet 2 in the project tracker and unlike excel I cannot use wild cards.

Sheet 1

Project Tracker sheet = Product category column that is a multiple selection drop down (what I traditionally use in the Match / Has component) options e.g. could contain Knees, Hips, Trauma,

Sheet 2: SME look up sheet

SME1Ref 1: Product category column = The individual business units that I wish to match against the contents of the project tracker e.g. Knees, Trauma

SME Ref 2: SME column (Business unit SME) e.g. Jeff for Knees, Marisa for Trauma.

Index match works fine on individual level, how to I adapt for multiple drop-down inputs - multiple contact outputs in a singular cell.

I have tried Join(collect coupled with HAS but no luck and not even sure what I want to do is possible.

Thanks for any assistance whether confirmation not possible or possible solutions ...

Paul Newcome

You cannot currently use a formula to output multiple usable contacts into a single cell. It will output as a text string which will not be usable for automations or any other contact specific actions.

Leibel S

@Stevey C

not if I understood you correctly.

sounds like you want all the contacts associated to be brought into the cell.

Unfortunately, JOIN(COLLECT(... does not work on Contact columns...

Paul Newcome

You cannot currently use a formula to output multiple usable contacts into a single cell. It will output as a text string which will not be usable for automations or any other contact specific actions.

Stevey C

Thanks for the insightful answers, hopefully, the Smartsheet team can create a command / function to circumnavigate the text string issues in the future.