Join Collect formula for multiple values

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

Find more posts tagged with

Formulas

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.