Join Collect formula for multiple values

Zeb Loewenstein

Is it possible to use a Join / Collect formula, which references a multi-select dropdown list in one sheet to find and query an array of values?

In Sheet 1, I'm querying a set of values however I can't seem to pull anything forward when multiple values are listed in the Client Name column. I'm trying to pull forward every Intake ID # (Sheet 2) for each Client Name (Sheet 1).

So ideally the empty cell below would read "OPS12, OPS33".

=JOIN(COLLECT([Client Name]@row, [Client Name]@row, CONTAINS(@cell, {Column_Client Name})), ",")

Sheet 1

Sheet 2

Zeb Loewenstein

I figured it out with the below formula. Including below in hopes this helps someone in the future!

=JOIN(COLLECT({Column_Intake ID #}, {Column_Client Name}, CONTAINS(@cell, [Client Name]@row)), " , ")

Genevieve P.

Hey @Zeb Loewenstein

Thanks for posting your solution! 🙂

Volodymyr L

That`s super cool solution

MazU

@Zeb Loewenstein thank you for your solution here, but i was wondering if you can help me

the values i am trying to return are returning duplicates

please can you advise?

Formula:

=JOIN(COLLECT({Building Database Range 3}, {Building Database Range 4}, CONTAINS(@cell, [Site Name]@row)), " , ")