Need formula to retrieve data from another sheet using multi-select field as reference
I need to find a way to reference/pull data from another sheet, using details listed in a multi-select field.
Here's the process and what I'm trying to do:
- Customer submits a form to sheet 1. The form contains a multi-select "Product name" field.
- example dropdown menu options:
- Audio book
- Audio book fiction
- Audio book fiction mystery
- example dropdown menu options:
- Once submitted, I need to populate a "Product code" column on sheet 1, based on any/all values that apply in a lookup table on another sheet (sheet 2).
- Sheet 1
- Product Name (multi-select dropdown)
- Product Code (populate from sheet 2)
- Sheet 2
- Product Name (text)
- Product Code (text)
- Sheet 1
I've only ever used the typical "Vlookup" and "index/match" formulas, but those don't work with multi-select fields. I'm starting to explore the "join/collect" and "contain" functions but found that erroneous values are sometimes returned because product names are often similar (see above).
Any recommendations?
Best Answer
-
The HAS function will work better than CONTAINS for multi-select drop-downs, I think.
But I'm a bit stuck on how to use the the Product Name (multi-select) as your lookup value. It sounds like you're essentially needing to parse out each selection, go look up the code for each, and then join them together. I'm not sure of a way to do that outside of a bunch of helper columns. :S If your drop down selections aren't too numerous, maybe that's workable (albeit not elegant)?
I'll be curious to see what others come up with!
Answers
-
The HAS function will work better than CONTAINS for multi-select drop-downs, I think.
But I'm a bit stuck on how to use the the Product Name (multi-select) as your lookup value. It sounds like you're essentially needing to parse out each selection, go look up the code for each, and then join them together. I'm not sure of a way to do that outside of a bunch of helper columns. :S If your drop down selections aren't too numerous, maybe that's workable (albeit not elegant)?
I'll be curious to see what others come up with!
-
Jennifer, thanks for the suggestions! I'll look into HAS and see what trouble i can get into :-)
I've seen others use screenshot visuals, so hopefully this helps
-
@Jennifer Kurtz Eureka! Seems like using the HAS function was the key.
=JOIN(COLLECT({Product Codes}, {Product Name}, HAS([Product Name]@row, @cell)), CHAR(10))
Thanks so much!
Sheet 1:
-
@Adam Joly That's so awesome — glad you figured it out!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!