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

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

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

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the FIND function is configured to search for the text "XST" in cell C5. If found, FIND will return a numeric position (i....

Excel does not provide a dedicated "contains" function, but you can create a custom function to test if a cell contains one or many strings with the LAMBDA function. LAMBDA functions do not require VBA, but are only...

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

In this example, the goal is to count codes that appear as substrings in a case-sensitive way. The functions COUNTIF and COUNTIFS are both good options for counting text values, but these functions are not case-...

The FILTER function can filter data using a logical expression provided as the "include" argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:
=...

Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need...

This is an experimental formula to strip characters from text. The experimental part is using character codes instead of regular characters as a way to make the formula case-sensitive, and providing a way to reverse the...

Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a range to ISNUMBER (i.e. an array), ISNUMBER will return an array of results. In the example,...

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