Lookup from a multi-select column, find (collect) all matching values

MCorbin

Not sure if this is possible, but if it is, this group would know!

I'm trying to lookup from my Requesting Department Column, which is a multi-select column, to another sheet that has the department head for each department, so that I can list their names in the "Responsible for" column (contact column).

If there's only 1 department listed, I can pull the name with an Index/Match.... but is there any way to collect all the names if there are multiple departments?

Paul Newcome

There is a way to pull all of the names, but they won't be listed as multiple contacts. Unfortunately it isn't currently possible to use a formula to output multiple contacts into a single cell.

If you do not need them to be listed as contacts then you can use something along the lines of...

=JOIN(COLLECT({Other Sheet Column To Pull From}, {Other Sheet Department Column}, CONTAINS(@cell, [Requesting Department]@row), ", ")

Paul Newcome

There is a way to pull all of the names, but they won't be listed as multiple contacts. Unfortunately it isn't currently possible to use a formula to output multiple contacts into a single cell.

If you do not need them to be listed as contacts then you can use something along the lines of...

=JOIN(COLLECT({Other Sheet Column To Pull From}, {Other Sheet Department Column}, CONTAINS(@cell, [Requesting Department]@row), ", ")

MCorbin

Thanks, that's what I suspected. But I wanted to be sure :-)

Paul Newcome

Sure thing. 👍️

Erin Kim

@Paul Newcome Do you know if there is now a way to pull in multiple contacts?

Paul Newcome

@Erin Kim There is still no way to pull in multiple contacts.

Erin Kim

Thanks @Paul Newcome . maybe you can help me out in a different way then. I have the below formula to pull in names from a contact list but there is no space between the lists from different cells.

example: pulls in MikeTimJoe when I would want: Mike, Tim, Joe

=JOIN(COLLECT({Site Executive List Range 2}, {Site Executive List Range 1}, CONTAINS(@cell, [Facility (PCD)]@row)))

Paul Newcome

@Erin Kim You would need to specify a delimiter in the second portion of the JOIN function.

=JOIN(COLLECT({Site Executive List Range 2}, {Site Executive List Range 1}, CONTAINS(@cell, [Facility (PCD)]@row)), delimiter)