0

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.

enter image description here

6
  • for a faster and more complete answer, it would be convenient to have the link to your file and also know what result you would like to obtain
    – Daniele
    Aug 10, 2022 at 8:58
  • @Daniele Thanks for your answer. I just updated the question. Here is the sample sheet I made. Thanks!
    – Rex Nguyen
    Aug 10, 2022 at 9:20
  • that formula is set to be dragged, note that a range of type C2:C has NOT been provided in this part: REGEXMATCH(C2...
    – Daniele
    Aug 10, 2022 at 10:42
  • @Daniele I tried that. But it doesn't seem to work. i.stack.imgur.com/5SnVA.png
    – Rex Nguyen
    Aug 10, 2022 at 10:51
  • yes, it doen't work because that formula didn't foresee it... I'm searching for an alternative
    – Daniele
    Aug 10, 2022 at 11:03

1 Answer 1

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.

5
  • There is something I do not understand ... the column to be filled and which is therefore initially empty is precisely column D, so why do you use it in the formula you gave? (if column D was empty, your formula would give an empty result)
    – Daniele
    Aug 12, 2022 at 9:08
  • Edited the answer. It was unclear which columns in your sample spreadsheet are sample data and which show the desired results. The answer now assumes that you simply want to array enable the formula you already have in cell D2. Aug 13, 2022 at 20:19
  • "To do the same automatically down the column..." I'm sorry, I think there is an error ... in this formula too you refer to column D, creating a circular dependency
    – Daniele
    Aug 14, 2022 at 2:09
  • Thanks Daniele, edited the answer. Aug 14, 2022 at 6:49
  • a beautiful piece of code !
    – Daniele
    Aug 14, 2022 at 8:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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