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:

  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?

Best Answer

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭
    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

  • Jennifer Kurtz
    Jennifer Kurtz ✭✭✭✭
    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!

  • Adam Joly
    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
    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
    Jennifer Kurtz ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!