VLOOKUP with Multi-Select Pick List Column

Options
Dennis W
Dennis W
edited 11/16/21 in Formulas and Functions

Hey there! I have an order form that allows users to select multiple products from a multi-select dropdown list. These values are contained in that column once the form is submitted.

On another sheet, I have the prices for each product listed.

I'd like to do a VLOOKUP for each item ordered, within the sheet with all the prices, to return a price for each product, but clearly I can't specify multiple values to search. I think I once solved this, but can't find my reference sheet.

I only have maybe 15 products, so I can easily set up 15 different lookups, each with their own destination cell, then sum these in a "Total Order Value" column.

Thanks, Dennis

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Dennis W

    If you don't mind building out multiple lookups to the other sheet per-item, then you can use the HAS function to see if it has a specific value, then use an INDEX(MATCH to return the associated price.

    For example:

    =IF(HAS([Items Ordered]@row, "Item 1"), INDEX({Column with Prices}, MATCH("Item 1", {Column with individual Items})), "")

    Then you can add together all of the 15 options in one formula by putting + between them:

    =IF(HAS([Items Ordered]@row, "Item 1"), INDEX({Column with Prices}, MATCH("Item 1", {Column with individual Items})), "") + IF(HAS([Items Ordered]@row, "Item 2"), INDEX({Column with Prices}, MATCH("Item 2", {Column with individual Items})), "") + IF(HAS([Items Ordered]@row, "Item 3"), INDEX({Column with Prices}, MATCH("Item 3", {Column with individual Items})), "") ... etc

    Does that make sense?

    Let me know if it works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Dennis W

    If you don't mind building out multiple lookups to the other sheet per-item, then you can use the HAS function to see if it has a specific value, then use an INDEX(MATCH to return the associated price.

    For example:

    =IF(HAS([Items Ordered]@row, "Item 1"), INDEX({Column with Prices}, MATCH("Item 1", {Column with individual Items})), "")

    Then you can add together all of the 15 options in one formula by putting + between them:

    =IF(HAS([Items Ordered]@row, "Item 1"), INDEX({Column with Prices}, MATCH("Item 1", {Column with individual Items})), "") + IF(HAS([Items Ordered]@row, "Item 2"), INDEX({Column with Prices}, MATCH("Item 2", {Column with individual Items})), "") + IF(HAS([Items Ordered]@row, "Item 3"), INDEX({Column with Prices}, MATCH("Item 3", {Column with individual Items})), "") ... etc

    Does that make sense?

    Let me know if it works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Dennis W
    Options

    Genevieve, I figured out the "HAS" function, but hadn't thought about combining all items in one formula. Not sure that's going to work, though, as I also need to multiply quantities for each item, so probably need 15 different formulae and 15 different destination cells. Dennis

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!