How do I return a cell value if a cell contains text from another cell?

Imagine I have two tables here. Left side is the reference table while the other one lookups values based from the reference table. what is formula should I use in column Product B to show these similar results?

I tried this formula but it doesn't work:

=JOIN(COLLECT({Reference A}, {Reference B}, contains(Product A@row),{ReferenceB}, ", ")

Find more posts tagged with

Accepted answers

Nick Korna

Are you trying to do it on the same sheet rather than a separate one?

In which case it would be:

=JOIN(COLLECT([Reference A]:[Reference A], [Reference B]:[Reference B], CONTAINS([Product A]@row, @cell)), ", ")

Example on same sheet:

Doing it cross sheet (using original formula):

All comments

Drey

I wanted to use contain since Reference B column has multiple values and I cannot directly lookup based on specific value. Thanks in advance for the help.

Alter your formula slightly to use @cell in the CONTAINS portion of your formula:

=JOIN(COLLECT({Reference A}, {Reference B}, CONTAINS([Product A]@row, @cell)), ", ")

This should give you the desired result.

Drey

@Nick Korna error. incorrect argument

Nick Korna

Are you trying to do it on the same sheet rather than a separate one?

In which case it would be:

=JOIN(COLLECT([Reference A]:[Reference A], [Reference B]:[Reference B], CONTAINS([Product A]@row, @cell)), ", ")

Example on same sheet:

Doing it cross sheet (using original formula):

Drey

@Nick Korna Thanks. i tried it first in the table I posted and it worked. it also works across sheets. it just needs to save and refresh before the result goes out.

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.