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...

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, use...

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...

In this example, criteria are entered in the range F5:H6. The logic of the formula is:
item is (tshirt OR hoodie) AND color is (red OR blue) AND city is (denver OR seattle)
The filtering logic of this formula (the...

Sometimes you encounter data that mixes units directly with numbers (i.e. 8km, 12v, 7.5hrs). Unfortunately, Excel will treat the numbers in this format as text, and you won't be able to perform math operations on such...

The core of this formula is the MODE function, which returns the most frequently occurring number in a range or array. The rest of the formula just constructs a filtered array for MODE to use in each row. The expanding...

The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so:
=IF(ISNUMBER(B4)
For any number less than the value in "input", the formula will return TRUE and the...

Data validation rules are triggered when a user adds or changes a cell value.
The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation.
Be aware...

The MATCH function checks all values in the named range "data" against values in "filter"
ISNUMBER converts matched values to TRUE and non-matched values to FALSE
IF uses the array output in #2 above to filter...

Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need...

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...

This formula depends on a TRUE or FALSE result from a logical test, where FALSE represents the value you are looking for. In the example, the logical test is data="red", entered as the lookup_array argument in the...

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a...

The core of this formula is the INDEX function, with AGGREGATE used to figure out the "nth match" for each row in the extract area:
INDEX(data,nth_match_formula)
Almost all of the work is in figuring out and...