A better way to lookup & categorize data besides IFS and REGEXMATCH-CodePudding

I am looking for a way to categorize data based on the keywords it contains.

Right now, I am using IFs with RegexMatch. But it's quite clustered & makes the sheet heavy when I have a larger amount of categories and keywords.

The formula I am using:

=IFS(REGEXMATCH(A2,$F$2),$E$2,REGEXMATCH(A2,$F$3),$E$3)

If anyone knows any better/cleaner way or different formula to streamline this process, please let me know. Thanks so much!

CodePudding user response：

to lookup things, there is nothing better than vLOOKUP...

try:

=VLOOKUP(REGEXEXTRACT(A2, TEXTJOIN("|", 1, E:E)), E1:F, 2, 0)

CodePudding user response：

try:

=ARRAYFORMULA(IF(A2:A="",,TRIM(FLATTEN(QUERY(TRANSPOSE(IFERROR(
 IF(REGEXMATCH(A2:A&IFERROR(SEQUENCE(1, COUNTA(F2:F))/0), 
 IFERROR(SEQUENCE(COUNTA(F2:F))/0)&TRANSPOSE(F2:F)), E2:E, ))),,9^9)))))