Collect and display associated numbers

Hello,

I'm looking for a formula to use to reference another sheet and collect and display the different User Story numbers associated with and OMNI #. I was thinking maybe VLOOKUP or INDEX with COLLECT or MATCH but I can't get it to work. Here is the data set:

So if the formula works correctly, the row with OMNI002800 would display, "US5208899, US5275369". Purple would have 2 User Stories, green would have 1, gold would have 5 and so on...

Any assitance is appreciated!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Assuming your screenshot is of your reference table, the following would go on the other sheet:

=JOIN(COLLECT({Reference Sheet Story Column}, {Reference Sheet OMNI Column}, @cell = [OMNI Column Name]@row), ", ")

All comments

Paul Newcome

Assuming your screenshot is of your reference table, the following would go on the other sheet:

=JOIN(COLLECT({Reference Sheet Story Column}, {Reference Sheet OMNI Column}, @cell = [OMNI Column Name]@row), ", ")

Dakota Haeffner

@Paul Newcome

I was hoping that would be a soft ball haha! Thanks Paul worked like a charm.

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