VLOOKUP with Multi-Select Pick List Column

Dennis W

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

Genevieve P.

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

Genevieve P.

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

Dennis W

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