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

Drey ✭✭
edited 11/15/23 in Formulas and Functions

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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):


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!