Alright, there's a lot of similar Q&As out there but I can't get this figured out. I'm trying to categorize the cells in column A according to the list of keywords in column B. I've tried a wide variety of excel and sheets solutions but can't seem to get this.

C2 is an example. How does one create a list of matching tags/categories based off a list?

This is the closet I've gotten:

C2: =TEXTJOIN(", ",TRUE,REPT(b$2:b$99,ISNUMBER(SEARCH(b$2:b$99,A2))))

I know there must be some kind of array formula I can plug in.

My sheet: https://docs.google.com/spreadsheets/d/1_Nnk7c7JMcacJh6r_TFCLi3FsnuPxzf5Sv-YYwcRxS8/edit?usp=sharing

enter image description here


Try this in C2 and copy down as far as you need:

=TEXTJOIN(", ",TRUE,ArrayFormula(IF(ISNUMBER(FIND(B$2:B$99&" ",A2&" ")),B$2:B$99,"")))

The ArrayFormula is checking to see which items from B$2:B$99 exist in the cell (i.e., tested by ISNUMBER, since anything found will have a start location and anything not found would return an error).

I've appended &" " to both the check range and the cell to check, so that "back" is not considered found in something like "backfly." If you want "back" to be considered found if it is part of a word like "backfly," just remove the concatenated space from the range and cell. However, that will also find that "hat" is part of "what," which likely isn't what you want.

Addendum 11/02/18: Be sure to see additional notes below that may apply to your situation.

  • I'll need to read the docs more closely to truly understand what's going on here [even with your awesome notes]....but boy howdy, it works. Oct 23 '18 at 16:07
  • 1
    Kyle, if you need specific clarification (i.e., "How does X part work?") let me know and I'll try to break it down. Fortunately, it's a pretty short formula for the power it packs.
    – Erik Tyler
    Oct 23 '18 at 20:44
  • 1
    To further clarify in the meantime, the range that the TEXTJOIN is joining is an array of items tested by IF. The test (in English) is "IF you can FIND an item from the B range in the A cell, include that item from the B range in the array, but IF you can't FIND it (i.e., the FIND location ISn't a NUMBER), insert a null there." The TRUE in TEXTJOIN says, "Don't include nulls." So you're left with just the matches.
    – Erik Tyler
    Oct 23 '18 at 20:51
  • If any future people stumble across Erik's awesome answer in the future, put lower() around A2 if your tag words are a different case than your original text. Oct 31 '18 at 21:44
  • 1
    Kyle, if you want truly case-agnostic searching, you can just change FIND to SEARCH in the formula instead.
    – Erik Tyler
    Nov 2 '18 at 5:22

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.