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

Drey

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

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

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.

Drey

@Paul Newcome

Nick Korna

@Drey,

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.