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

lvolpe

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!

Paul Newcome

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.

Paul Newcome

If you are wanting to populate useable contacts, then you will only be able to populate one per row. It is not currently possible to populate multiple useable contacts within a single cell.

lvolpe

Thanks, Paul! If I converted the contact cell to just text, so that I could see the name of the assigned person, would that work?

Paul Newcome

You can leave it as a contact type cell. It just won't be able to use it as a contact for things such as automations or anything that is filtered by user.

Exactly how do you have your reference table set up? Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with mock data as needed?

lvolpe

Sure can. See below. Ideally, the columns titled CPL, Circuit, and Region would auto-populate the values that correspond to the counties that are selected in the master sheet.

Paul Newcome

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.

lvolpe

Three cheers - it worked perfectly. Thank you!

Paul Newcome

Happy to help. 👍️

Melanie Proebstel

@Paul Newcome Can you assist me with something along these lines? I have a mutli-select dropdown with 7 options. When these two options are selected, I am trying to create an IF statement that will populate the cell value to 12.5.

I am not having luck with an formula.