0

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.

2
  • Welcome to Web Applications. Please show what you tried and add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Jun 22 at 4:35
  • It sounds like you are asking several questions: 1 how to find category names by a list of IDs, 2 how to create a drop-down list, 3 how to make a spreadsheet work like a custom database system. I am answering the first question below. You may want to ask the two other questions separately, and consider sharing a publicly editable sample spreadsheet. Also see What is the XY problem? Jun 22 at 6:24
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.

1
  • Thaks for this. The id's are actually unique 12 byte object ID's from MongoDB. I was just making up values for the mock example above.
    – occam98
    Jun 22 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.