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
-
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.
Answers
-
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.
-
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?
-
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?
-
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.
-
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.
-
Three cheers - it worked perfectly. Thank you!
-
Happy to help. 👍️
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!