VLOOKUP and/or COLLECT/ JOIN?

Hi! We'd like to have visibility to all clients associated with a given person. Here's a simplified example. Current Data:

Screen Shot 2021-02-26 at 7.28.57 AM.png

Here's the result I'm looking for on a separate sheet - where the Primary Name is populated via an external form, and a formula looks up the Primary Name and collects all Clients associated with the Primary

Screen Shot 2021-02-26 at 7.29.22 AM.png

I was thinking VLOOKUP, or COLLECT, JOIN formula, or a combo of?

Thanks in advance for any ideas!

Find more posts tagged with

Accepted answers

Paul Newcome

You are going to want a JOIN/COLLECT.

=JOIN(COLLECT({Clients Range}, {Primary Name Range}, [Primary name]@row), ", ")

All comments

Paul Newcome

You are going to want a JOIN/COLLECT.

=JOIN(COLLECT({Clients Range}, {Primary Name Range}, [Primary name]@row), ", ")

DLM

This is perfect, thanks so much Paul, much appreciated!!!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.