INDEX() MATCH() Formula with some cells containing multiples items?

I am cross-referencing sheets to auto-populate the [Assigned To - Organisation] column in the {Action Tracker} sheet. I utilise the values in the [Assigned To - Individual] column to extract the Organisation from the {Project Directory[Organisation]} sheet/column. I have achieved this using INDEX, MATCH formula, see below,

=INDEX({Project Directory[Organisation]}, MATCH([Assigned To - Individual]@row, {Project Directory Range 1}, 0)). Which works fine when there is only one item within the email cell.

However, when one cell in the [Assigned To - Individual] contains two individuals' email addresses, I would require extracting these people's organizations and joint their results in the respective [Assigned To - Organisation] cell.

So far, the formula I figure out within my novice capabilities looks like this;

=JOIN(INDEX({Project Directory[Organisation]}, MATCH(CONTAINS([Assigned To - Individual]@row, {Project Directory Range 1}), 0)))

But the result in the [Assigned To - Organisation] column is #NOT MATCH

Thank you very much in advance for those taking the time to help me with this.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!