excel - Find if any of a list of substrings is present in a string

Question

My problem is that I have a long list of titles that need to be categorised based on keywords. There are 10 categories, that each have between 2 and 16 keywords.

I created a table to do the analysis in like so:

Title	cat 1	cat 2	etc.
How to foo the bar
Wax on, wax off

Answer 1

You may try:

Formula in B2:

=--(SUMPRODUCT(--ISNUMBER(SEARCH(F$2:INDEX(F:F,COUNTA(F:F)),$A2)))>0)

This does not account for possible false positive when a substring is found in a larger substring.