Comparing string with

0
votes

In Google Sheets, I'm trying to indicate whether each cell in a specific column (the "Target Column") contains any of the words listed in a group of cells (the "Word Warehouse"), organized into different "Categories". If the word appears in the Word Warehouse, I would like the cell to spit out the category that it's listed under, e..

See attached sheet here: https://docs.google.com/spreadsheets/d/10jiicpOpplaURrF0UtTi9HM2TFP04-tinynpnFef2mE/edit#gid=0

Thanks in advance!

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

Try:

=INDEX($2:$2,1,MAX((ISNUMBER(SEARCH(IF(LEN($D$3:$F$7),$D$3:$F$7),A3))*COLUMN($D$3:$F$7))))

enter image description here

1
votes

Alternatively, you can also try

=ArrayFormula(iferror(VLOOKUP(regexextract(proper(A2:A), textjoin("|", 1, D2:F)), split(flatten(D2:F&"_"&D1:F1),"_"), 2, 0)))

This formula allows for an array output, so dragging down is not necessary.

Example