dropdown multi-select and lookups -- vlookup, join, collect, index, match???

joelmold

Hello, I'm having trouble understanding what function I should use to do lookups when there is more than one matching value. I know how to use vlookup, at least well enough to know it won't work in this case. Here's what I'm trying to do:

On one sheet (sheet1) I have a dropdown multi-select with about 80 different options. On another sheet (sheet2) I have all 80 options listed as rows with additional info added such as contact info. When a user fills out a form on sheet1 and selects multiple options, I would like to return ALL values matching everything they list.

For example, a user selects "Apple" and "Banana" in the multi-select dropdown on sheet1. Sheet2 has this info:

Apple, Red, redapple@gmail.com

Apple, Green, greenapple@gmail.com

Banana, Yellow, banana@gmail.com

I want to lookup Apple and Banana, and return "Red, Green, Yellow" and "redapple@gmail.com, greenapple@gmail.com, banana@gmail.com" to the corresponding row in sheet1.

Hopefully this example makes sense. Can someone help me understand how this works?

Thank you!

joelmold

Ok, I found a useful comment here that looks similar to what I'm trying to do: https://community.smartsheet.com/discussion/68356/lookup-from-a-multi-select-column-find-collect-all-matching-values

Maybe I complicated things with my example. On my main sheet, users can select multiple values in the "Units" column. I want to do a lookup based on everything they list in Units, and bring back the corresponding tags for those units. Here is the formula I used, what did I do wrong?

Report Lookup Department Range 1 = other sheer range to pull tags from

Report Lookup Department Range 2 = other sheet Units column

=JOIN(COLLECT({Report Lookup Department Range 1}, {Report Lookup Department Range 2},CONTAINS(@cell,[Units]1@row),",")

Bonus question: If the user can select Units in two columns (one single-select primary unit, and the other multi-select additional units), is there a way to do a lookup like above on both cells and return the values to a single cell? If not, I could do the lookup in two columns, one for primary unit and one for additional units.

Thank you!

Paul Newcome

The problem with your formula is:

[Units]1@row

The @row reference replaces the row number and tells the formula to evaluate the cell on whatever row the formula is residing.

So you would want o use @row OR 1, but not both.

To combine the single and multi select column results into the same cell, you would just have to write the same formula except point it at the singe select and then add the two formulas together with your delimiter in between.

=JOIN(COLLECT(single select column formula), ", ") + ", " + JOIN(COLLECT(multi select column formula), ", ")