Using Join, collect, and contains with a multiselect column

kioshi43

Hello,

I'm trying to see if I can extract a location's email address into a multi contact column but I seem to be hitting a bit of a wall when someone selects two or more entries in their location. The contact emails are listed on a separate sheet but both sheets share the same location naming.

=JOIN(COLLECT({Location Email}, {Location}, CONTAINS(Location@row, @cell)), CHAR(10))

The formula works when there is an individual location added to the location column in the sheet I'm working with but as soon as you select a second location, I get a blank.

Anyone have any thoughts?

Genevieve P.

Hi @kioshi43

The Location@row portion of your formula that references a cell with a value to search for in your {Location} cross-sheet reference would need to be a single selection.

There currently isn't a way to parse out multiple selections in one cell by referring to it and then search for each of the values individually in another column; you would need each value to be split out individually.

You could potentially search for exact matches... for example if your Location@row had 2 locations selected, then you could search the {Location} column for an exact match. This would skip over individual selections and search for the unique combination of just these two locations, no more, no less. Does that make sense?

Cheers,

Genevieve