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

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):
Answers

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.

@Nick Korna error. incorrect argument

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):

@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.
Help Article Resources
Categories
Check out the Formula Handbook template!