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

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

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

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

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

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

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

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

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

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

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10...

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

In this example, we have a list of of colors in a named range called "things" (E5:E9). We want to check the text in column B to see if it contains any of these colors. If so, we want to return the name of the first...

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