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

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 ...


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!