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))," / ")

Leibel S

@Charlie T.

I think you are looking for something like the below:

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

Charlie T.

Hi Leibel Shuchat,

Your response is highly appreciated.

I used your formula, which returns the right organisations, but again only over those cells with only one individual. In the case of those cells with multiples email addresses, the cell remains blank.

Would this response light you on any idea what might be missing here?

Cheers.

Leibel S

Try it this way:

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

Charlie T.

Impressive Leibel Shuchat, this one did the job.

Thank you very much.

😎