ArrayFormula and Categorize Data in Google Sheets using Regular Expressions

Question

I'm following this post to categorize data in Google Sheets using regular expressions.

The person who wrote the answer was able to get the formula to automatically write down the category in column D.

I assume they used ArrayFormula to achieve it, but I can't seem to get it done. Or they just paste the formula to each cell of the column. I'm not sure.

The formula:

=IFERROR(INDEX(A$2:B,MATCH(TRUE,ARRAYFORMULA(REGEXMATCH(C2,INDIRECT("A"&2&":A"&3+COUNTA(B$2:B)))),0),2))

Here is the sample Sheet I made as an example.

What I am looking for is to categorize data in column C based on the keywords in column A.

Does anyone know how to do it? Thanks so much.

Answer 1

To find the category in column B where the regex in column A matches the phrase in column C, use filter(), like this:

=+filter( B$2:B, regexmatch(C2, A$2:A) )

Put the formula in cell D2 and copy it down.

To do the same automatically down the column, use vlookup() with wildcards:

=arrayformula( 
  iferror( 
    vlookup( 
      "*" & regexextract(C2:C, "(?i)" & textjoin("|", true, A2:A)) & "*", 
      A2:B, columns(A2:B), false 
    ) 
  ) 
)

To find the word in column C that matches the regex in column A per the category given in column D, use vlookup(), like this:

=arrayformula( iferror( regexextract( C2:C, "(?i)" & vlookup(D2:D, { B2:B, A2:A }, 2, false) ) ) )

Put the formula in cell E2.