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

In this example, the goal is to count the values in column B listed in the range E5:E7. One way to do this is to give the COUNTIF function all three values in the named range things (E5:E7) as criteria, then use the...

Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range "things":
--ISNUMBER(SEARCH(things,B4)
The SEARCH function returns the position of the value if...

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

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

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

The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal "contains" problem...

At the core, this is an INDEX and MATCH function.
Inside the MATCH function, we use the SEARCH function to search cells in column B for every listed keyword in the named range keywords (E5:E14):
SEARCH(keywords,B5...

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

The goal in this example is to use a formula to report which things exist a cell. The list of things to check for is in the named range things (E5:E9). The result is returned as a comma separated text string.
The first...

This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:
1/(ISNUMBER(FIND(G6,files)))
Inside this snippet, the FIND...

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

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

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