COLLECT contains ?

Hello, I have 2 sheets. In sheet 1 I have column 1A where each cell contains multiple alphanumeric codes separated by a semicolon

(column multiple codes). In sheet 2 I have column 2A where each cell contains one alphanumeric code (column single code) and column 2B where each cell contains the code description (column code description).

I am looking for a formula to be put in sheet 1 that collects all the descriptions from column 2B if one of the alphanumeric codes in the cell in column 1A is present in column 2A. Any idea on how to do this?


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this...

    Insert a helper column (multi-select dropdown) with the following:

    =SUBSTITUTE([Column 1A]@row, ";", CHAR(10))

    Then in Column 1B you can use this:

    =JOIN(COLLECT({Sheet 2 Column 2B}, {Sheet 2 Column 2A}, HAS([Helper Column]@row, @cell)), "///")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!