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!