Need formula for multi-select dropdown column to produce multiple values in another column


I've been trying on several formulas for size (VLOOKUP, INDEX, MATCH, and every combo in between) to assign an organization to one or more contacts based on the counties that are served by that organization. For example, I have a multi-select dropdown column where one or more counties can be selected, and I want the assigned column to automatically populate the individual(s) that oversee those counties.

I've created a separate table in a reference sheet, hoping that would help do the trick; the VLOOKUP piece works for the front half, but I can't seem to get the names to populate the assigned column. Any suggestions appreciated!

Thanks for helping me solve this one!

Best Answer

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

    Ok. To pull multiple people you are going to want something along the lines of...

    =JOIN(DISTINCT(COLLECT({Reference Sheet CPL Column}, {Reference Sheet County Column}, HAS([Source Sheet County Column]@row, @cell))), CHAR(10))

    CHAR(10) is a line break. You can change that portion to be whatever you want your delimiter to be. If you stick with CHAR(10), be sure to enable text wrapping.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!