How to do lookup for multi select dropdown cell to return results for all selected items in cell?

Xlookup

Hi I'm trying to do a lookup or index/match or whatever that can search all values in a dropdown list cell with multiple values and return multiple values.

Sheet 1 screenshot (yellow highlight is where I want formula to be):

Sheet 2 screenshot (contains table of lookup values):

I'm trying formula from this question from before: https://community.smartsheet.com/discussion/comment/346853#Comment_346853

But I'm not getting it to work and get #UNPARSEABLE error

=JOIN(DISTINCT(COLLECT({DocumentName}, {RowID}, CONTAINS(@cell, GroupedList@row ))), SUBSTITUTE($LineBreak$1, "-", ""))

Paul Newcome

It is working in my sheet. I can confirm this works with cross sheet references as well.

DKazatsky2

Hi @Xlookup,

Give this a try.

=JOIN((COLLECT({DocumentName}, {RowID}, CONTAINS(@cell, GroupedList@row))), CHAR(10))

Hope this helps,

Dave

Xlookup

https://community.smartsheet.com/discussion/comment/466348#Comment_466348

I get multiple results from this and seems fairly successful, is it possible to organize these results into a multi select dropdown option?

Also this formula seems to be pulling EXTRA results in the formula. Like more results than the total of selected options in a single dropdown cell. How do I fix that? I'm getting values pulled from like 80-90 rows up from where I'm doing the lookup

Edit: Can't figure out issue where formula is pulling EXTRA values. For example, I have a cell that only has 1 drop down select option and for some reason this formula returns TWO document names back which can't be correct

Paul Newcome

Give this a try:

=JOIN(COLLECT({DocumentName}, {RowID}, HAS(GroupedList@row, @cell)), CHAR(10))

Xlookup

https://community.smartsheet.com/discussion/comment/466522#Comment_466522

Switching from CONTAINS to HAS results in a blank cell, no values returned. Sticking with CONTAINS for now

Paul Newcome

It is working in my sheet. I can confirm this works with cross sheet references as well.

Xlookup

https://community.smartsheet.com/discussion/comment/466536#Comment_466536

Thank you, this worked. Now do you have any insight on how to get my formula column to populate as a multi select cell? It's currently formatted as a text/number column.

Paul Newcome

You would just need to change the column type to a dropdown and allow multiple selections. The CHAR(10) delimiter in the formula is the line break which is the same delimiter used on the back-end. Once you adjust the column type, it should look the same as the [Formula] column in my last screenshot.