2

How do I match a category list(column D) with 3 columns of keywords (columns A B C) to a list of strings(column H) and have the result of column I?

I was thinking vlookup and tried something like this, but looks like it doesn't work: 

=vlookup("*"&A2:A&"*"&B2:B&"*"&C2:C&"*",H2:H,1)

I was also considering QUERY, but I don't think that can match by row either.

Any help is appreciated!

spreadsheet: https://docs.google.com/spreadsheets/d/1AHT5d4KkZ49csitkICUd7ANwjWMZXk9GAZA_5pVZlrs/edit#gid=0

For example:

enter image description here

sojim2
  • 1,245
  • 2
  • 15
  • 38

1 Answers1

1
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(
 IFERROR(VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,1), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0))&
 IFERROR(VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,2), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0))&
 IFERROR(VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,3), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0)), 
 TEXTJOIN("|", 1, UNIQUE(IFERROR({
 VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,1), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0);
 VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,2), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0);
 VLOOKUP(INDEX(IFERROR(REGEXEXTRACT(IFERROR(SPLIT(H2:H, "-")), 
 TEXTJOIN("|", 1, A2:C))),,3), {{A2:A;B2:B;C2:C},{D2:D;D2:D;D2:D}}, 2, 0)}))))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks for answering this and sorry about the mods fighting. One problem I found (I should have provided a better set of list in `H`) https://docs.google.com/spreadsheets/d/1AHT5d4KkZ49csitkICUd7ANwjWMZXk9GAZA_5pVZlrs/edit#gid=0 if I have `bling_bang_pay` it mistakenly categorizes it as `IncomeInequality` due to the `pay` keyword. I'd like it to match all available keywords. – sojim2 Sep 05 '19 at 20:47
  • well, yes that's kinda expected behavior. `bling_bang_pay` has no `-` so its treated as 1 phrase eg the `SPLIT` is not applied and `REGEXEXTRACT` is in mode of finding partial matches not full matches (full matches fould be: `"^"&TEXTJOIN("$|^", 1, A2:C)&"$"` instead of: `TEXTJOIN("|", 1, A2:C)` – player0 Sep 05 '19 at 22:10