Match any part of multi select to another multi select on another sheet


I have two separate sheets that each have multi select columns and I want to join a particular column if it has ANY mutual matches between the two.

For example, on sheet 1, A B C are selected in the multi select and I want to find any matches on sheet 2 which contain anything selected from sheet 1. The column on sheet 2 is also a multi select column and can have any combination of these selected.

So if A, B, C are selected on sheet 1 and B, D, E are selected on sheet 2, this should trigger a match.


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure I see where the JOIN comes into play. Are you able to provide screenshots with manually entered mock data that shows what exactly you are trying to accomplish?

  • Thank you for your response. I have attached a screenshot below and hopefully this explains it a little better. On the screenshot below, let's say that the Example 1 is on its own sheet. What I want to do on sheet 1 is collect and join any data in the Data 1 column if any part of the multi select in Example 1 matches any part of the multi select in Example 2. Since they both contain C, the text from the Data 1 should be collected and joined into the formula on Sheet 1.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What exactly would the output be though when you say "Collect and join"?

  • Here is a new screenshot. The x column is the break between Sheet 1 and Sheet 2.

    On Sheet 1, for each row there will be data selected in the Example 1 multi select column. I want the output column to collect and join any data on the Data 1 column in Sheet 2 where any part of Example 1 matches any part of Sheet 2.

    Since C matches both rows in Example 2, the expected output on the Output column would be String 1, String 2.

    If Example 1 only contained one selection then this would be really simple since I could just do a JOIN(COLLECT()) to match Example 1 to Example 2 but the issue I am facing is that I am trying to match ANY selection in Example 1 to Example 2 to collect the data in the Data 1 column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    How many possible selections could be made in a single cell?

  • Thank you Paul, this works perfectly and I appreciate the help!

  • @Paul Newcome I've poured over several threads about JOIN() formulas you've helped others with, attempting to parse values from a multi-selection cell [Supply SKU]. I want to use those values to lookup another sheet to bring in a cost. The abovementioned A,B,C formulas in this thread are the first to get the result I'm looking for however, I have up to 14 values that could be selected. Here are some examples of what I'm working with. Since I have so many values that could be returned, I feel I need to go back to the JOIN() (maybe JOIN(COLLECT())?) formula but can't seem to get it working past the 2nd value. I added in a delimiter "+" to see if that would help but to no avail. Any thoughts are greatly appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!