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

Charlie T.

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.

Leibel S

Try it this way:

=JOIN(COLLECT({Project Directory[Organisation]},{Project Directory Range 1},HAS([Assigned To - Individual]@row, @cell))," / ")