To categorize text using keywords with a "contains" match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is:
{=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(...

To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match.
This is an array formula and must be entered...

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions.
Context
Let's say you...

If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function.
Setup
Suppose you have a...

To disallow input that contains one of many things, you can use a custom data validation rule based the SEARCH function.
In the example shown, the data validation applied to B5:B11 is:
=SUMPRODUCT(--ISNUMBER(SEARCH(...

If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the columns...

To count the number of specific words or keywords that appear in a given cell, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. In the example shown, the formula in C5 is:
=SUMPRODUCT(--...

To extract text between parentheses, braces, brackets, etc. you can use a formula based on the MID function, with help from SEARCH function. In the example shown, the formula in C5 is:
=MID(B5,SEARCH("(",B5...

If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions.
Context
Let's say you have a...

To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions.
In the example shown the formula in C5 is:
=(SUMPRODUCT(--ISNUMBER(SEARCH...

To test a cell for one of several things, and return a custom result for the first match found, you can use an INDEX / MATCH formula based on the SEARCH function.
In the example shown, the formula in C5 is:
{=INDEX(...

To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function.
In the example shown, the formula in C5 is:
{=AND(...

To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is:
=IF(F5...

To find the position (row) of the last file revision in a table, you can use a formula based on several Excel functions: MAX, IF, ISERROR, ROW, and INDEX.
In the example shown, the formula in cell H6 is:
{=MAX(IF(...

To test if one of several values exists in a range of cells, you can use a formula based on the SUMPRODUCT function. In the example, the formula in cell D5 is:
=SUMPRODUCT(--(rng=B5:D5))>0
How the formula works...