Creating a multiselect lookup row in Google sheets

Question

I have a sheet of activities that looks something like this

Activity Name	Category Ids
Activity 1	123456, 98756, 345677

I have a second sheet of category ids

Category Id	Category Name
123456	Category1
98756	Category 2
345677	Category 3

Here's a link to a sample spreadsheet

What I would like to do is create a column in the activity sheet that displays the names of all of the categories that are in the category Ids field for that activity.

Is this possible in Google Sheets? So far, I haven't even been able to get a simple lookup to work on multiple id's in the id's field (see my spreadsheet). I've also looked at this article on implementing multiple-select dropdown lists, but haven't implemented it because I am not sure how to deal with the fact that in my case, the dropdown list needs to be pre-populated with the values already in Category IDs.

Answer 1

The category IDs are of varying lengths, which means that simple string matching cannot be used because of the risk of matching a key like 1234 with a key like 123456. To get started, try filter() with regexmatch(), like this:

=textjoin( 
  ", ", 
  true, 
  filter( 
    Categories!B$2:B, 
    regexmatch(B2, "\b" & Categories!A$2:A & "\b") 
  ) 
)

Here is the same formula, adjusted to your sample spreadsheet:

=textjoin( 
  ", ", 
  true, 
  iferror( 
    filter( 
      Grade!B$2:B, 
      regexmatch( 
        regexextract(D2, "'(.+)'"), 
        "\b" & Grade!C$2:C & "\b" 
      ) 
    ) 
  ) 
)

The formula should go to cell Sheet1!F2. Copy it down to extend it to further rows.

To learn the exact regular expression syntax used by Google Sheets, see RE2.