Formula to search for a keyword from a keywords li

问题:

I have a list of texts (Info column) and list of categories (Category column).

I need an additional column next to info (Info Category) where I could categorize my texts by finding which category is mentioned inside my text. In SQL I did it like this:

SELECT info, Category as [Info Category]
FROM Info_List i LEFT JOIN Category_List c 
    ON i.Info like '%' + c.Category + '%' 

回答1:

To find the first matching category within some longer text in the Info column, use this standard formula in B2.

=IFERROR(INDEX($F:$F, AGGREGATE(15, 6, ROW($F$2:INDEX($F:$F, MATCH("zzz",$F:$F )))/ISNUMBER(MATCH("*"&$F$2:INDEX($F:$F, MATCH("zzz",$F:$F ))&"*", $A2, 0)), COLUMN(A:A))), "")

Fill down as necessary then fill right if you have multiple matching categories within the info.

    

Order your keywords in a descending manner. If you are only looking for the first category, you will find that CAT01 will be found before CAT015. A descending order can take care of this. Wrapping both the category words and the Info phrase in spaces will also reduce false positives but punctuation interferes. Note the false positive in row 11 where erat was found within placerat.