Explanation

The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like “?” and “*”. This means we can’t use IF by itself to test for a substring like “abc” or “xyz” that might appear anywhere in a cell . One option (seen in the example) is to create a logical test with ISNUMBER, SEARCH, and OR, then use the IF function to return a final result. Another approach is to use the COUNTIF function with the SUM function to create the logical test. Both approaches are explained below.

OR + SEARCH + ISNUMBER

The SEARCH function is designed to look inside a text string for a given substring. If SEARCH finds the substring, it returns the position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:

=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!

The ISNUMBER function . ISNUMBER returns TRUE for numeric values and FALSE for anything else:

=ISNUMBER(2) // returns TRUE
=ISNUMBER("a") // returns FALSE

We can use ISNUMBER to convert the result from SEARCH into a TRUE or FALSE value like this:

=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE

If SEARCH finds the substring, it returns the position as a number, and ISNUMBER returns TRUE. If SEARCH doesn’t find the substring, it returns an error, and ISNUMBER returns FALSE. This works fine, but the challenge in this problem is that we need to test for two substrings , not one. We can do this by using SEARCH and ISNUMBER twice inside the OR function:

=OR(ISNUMBER(SEARCH("abc",B5)),ISNUMBER(SEARCH("xyz",B5)))

Now if either of the expressions returns TRUE, the OR function will return TRUE and trigger the IF function. One way to simplify the formula a bit is to use an array constant and a single expression like this:

=OR(ISNUMBER(SEARCH({"abc","xyz"},B5)))

An array constant is a structure that holds multiple values. It works like a range in Excel, except the values in an array constant are hard coded. Because we are giving SEARCH two substrings, it will return two results. The ISNUMBER function will also return two results to the OR function, which will evaluate these results as before.

Note: the SEARCH function is not case-sensitive. If you need a case-sensitive option you can switch to the FIND function as explained here .

IF function

Putting this all together, we can use the formula above inside the IF function as the logical test like this:

=IF(OR(ISNUMBER(SEARCH({"abc","xyz"},B5))),"x","")

This is the formula used in cell D5 of the example. As the formula is copied down, it returns “x” if an email address contains either “abc” or “xyz” and an empty string ("") if not. You are free to adjust the IF formula to return whatever values you like.

Note: the IF function simply leaves an “x” in a cell as a marker. I f the goal is to retrieve all matching cells or records, see the FILTER function .

COUNTIF + SUM

Another way to solve this problem is with the COUNTIF function together with the SUM function like this:

=IF(SUM(COUNTIF(B5,{"*abc*","*xyz*"})),"x","")

The core of this formula is COUNTIF, which returns zero if none of the substrings is found, and a positive number if at least one substring is found. The twist is that we are giving COUNTIF more than one substring to look for in the criteria, supplied as an " array constant “. As a result, COUNTIF will return an array of counts, one count per condition. Because we are getting back an array from COUNTIF, we use the SUM function to sum all items in the array. The result goes into the IF function as the logical_test . Any non-zero number will be evaluated as TRUE.

Note that we are also using the asterisk (*) as a wildcard for zero or more characters on either side of the substrings. This is what allows COUNTIF to count the substrings anywhere in the text (i.e. this provides the “contains” behavior).

Notes

  1. If you are testing many values, you can use a range instead of an array constant to provide values to check. In Excel 2019 and earlier, using a range will make the formula an array formula that must be entered with control + shift + enter. In the current version of Excel, no special handling is required.
  2. The COUNTIF function will accept ranges only for the range argument; you can’t feed COUNTIF an array that comes from another formula. This can be a problem when working with dynamic array formulas , where it is more common to pass arrays from one formula to another. The OR + SEARCH + ISNUMBER formula does not have this limitation.

Explanation

The goal is to do something if a cell equals a given value. The most common way to solve this problem is with the IF function.

IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The generic syntax for IF looks like this:

=IF(logical_test,if_true,if_false)

The result from IF can be a value, a cell reference, or even another formula. In the worksheet shown, the goal is to identify rows where the color is “Red” by returning “x” as a marker. To accomplish this task, the formula in cell F5 is:

=IF(C5="red","x","")

In this formula, the logical test is this expression:

C5="red"

This expression returns TRUE if the value in C5 is “red” and FALSE if not. In cell F5, the result will be TRUE because C5 equals “red” but in cell F6 the result will be FALSE because C6 equals “Blue”:

C5="red" // returns TRUE
C6="red" // returns FALSE

The formula at this point looks like this:

IF(C5="red",

Next, we need to add a value when the result is TRUE and a value when the result is FALSE. Since we want to mark items when the color is “Red”, we provide “x” for the value to return if TRUE:

IF(C5="red","x",

Since we don’t want to display anything when the color is not “Red”, we provide an empty string (”"), for the value to return if FALSE. The final formula in cell F5 looks like this:

=IF(C5="red","x","")

The result returned by IF can be customized as needed. If an empty string ("") is not provided for value_if_false , the IF function will return FALSE when the color is not “Red”. Note that Excel is not case-sensitive by default. The expressions below will all return TRUE:

C5="Red" // returns TRUE
C5="RED" // returns TRUE
C5="red" // returns TRUE

If you need a case-sensitive formula, see the EXACT function .

Increase price if color is red

The result from IF does not need to be a hard-coded value. It can be a cell reference or another formula. For example, let’s say you want to increase the price of Red items only by 10%. In that case, you can use a formula like this:

=IF(C5="red",D5*1.1,"")
IF function example - increase price 10% if color is Red - 1

The test is the same as before (B6=“red”). If the result is TRUE, we multiply the original price by 1.1 (i.e. increase the price by 10%). If the result is FALSE, we return an empty string ("").