This formula is based on the simpler formula described here. It uses the SEARCH function to look inside B5 for using cell C4 as the search string. If SEARCH returns a number, it means the search string was found. In...

The SEARCH function returns the position of the search string when found, and the #VALUE! error if not found. We use this fact to test whether the search string is found by using the ISNUMBER function to "catch" valid...

The XLOOKUP function contains built-in support for wildcards, but this feature must be enabled explicitly by setting match mode to the number 2.
In the example shown, XLOOKUP is configured to match the value entered...

This formula uses two named ranges: things, and results. If you are porting this formula directly, be sure to use named ranges with the same names (defined based on your data). If you don't want to use named ranges,...

At the core of this formula, we build a list of row numbers for a given file. Then we use the MAX function to get the largest row number, which corresponds to the last revision (last occurrence) of that file.
To find...

At the core, this formula uses the SEARCH function to look for multiple strings inside a cell. Inside the left SUMPRODUCT, SEARCH looks for all strings in the named range "include".
In the right SUMPRODUCT, SEARCH...

The core of this formula is ISNUMBER and SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Here, we transpose the items in the named range "exclude", then feed the result to SEARCH as the "find text", with "data" as...

Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.
The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just...

Data validation rules are triggered when a user adds or changes a cell value.
This formula uses the SEARCH function to test user input for each value in the named range "list". The search logic is "contains" — when a...

Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only counts instances of different keywords.
The core of this formula is the ISNUMBER + SEARCH...

We want to test each cell in B5:B11 to see if it contains any of the strings in the named range things (E5:E9). The formula we're using in C5, copied down, is:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
This...

This formula relies on the AND function to test two conditions at the same time:
Count of words from named range inc is >0
Count of words from named range exc is =0
If both conditions are TRUE, the formula...

The key is this snippet:
ISNUMBER(SEARCH(things,B5)
This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula...

The foundation of this formula is the MID function, which extracts a specific number of characters from text, starting at a specific location. To figure out where to start extracting text, we use this expression:...

Context: you have a list of things in the named range "things" (E5:E8), and you want to check cells in column B to see if they contain these things. If so, you want to return the last item from"things" that was found...