Need formula to retrieve data from another sheet using multi-select field as reference

Adam Joly

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:

1. Customer submits a form to sheet 1. The form contains a multi-select "Product name" field.
   1. example dropdown menu options:
      1. Audio book
      2. Audio book fiction
      3. Audio book fiction mystery
2. 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).
   1. Sheet 1
      1. Product Name (multi-select dropdown)
      2. Product Code (populate from sheet 2)
   2. Sheet 2
      1. Product Name (text)
      2. Product Code (text)

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?

Jennifer Kurtz

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 Kurtz

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!

Adam Joly

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

Adam Joly

@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:

Jennifer Kurtz

@Adam Joly That's so awesome — glad you figured it out!!