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

lvolpe · 07/07/21

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 · 07/07/21

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 · 07/07/21

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 · 07/07/21

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 · 07/07/21

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 · 07/07/21

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 · 07/07/21

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 · 07/07/21

Three cheers - it worked perfectly. Thank you!

Paul Newcome · 07/07/21

Happy to help.️

Melanie Proebstel · 05/03/22

@Paul NewcomeCan 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.

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

Best Answer

Answers

Help Article Resources

Categories