Excel: How to Check if Cell Contains Text from List

You can use the following formula in Excel to check if a cell contains text from a list:

=IF(OR(COUNTIF(A1,"*"&$E$2:$E$8&"*")), "Yes", "No")

In this example, if cell A1 contains any of the text values in the range E2:E8 then it will return a Yes, otherwise it will return a No.

The following example shows how to use this formula in practice.

Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:

There are three teams in the list from Texas: Mavs, Spurs, and Rockets.

Suppose we’d like to create a new column that tells us whether each team is from Texas or not.

First, we’ll create a list of the Texas teams in column E:

Then we’ll use the following formula to check if the value in the Team column contains any of the text values in column E:

=IF(OR(COUNTIF(A2,"*"&$E$2:$E$4&"*")), "Yes", "No")

We can type this formula into cell C2 and then copy and paste it down to the remaining cells in column C:

Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Yes while all other rows receive a value of No.

Also note that we could return values other than “Yes” and “No.”

For example, we could use the following formula to return “Texas” or “Not Texas” instead:

=IF(OR(COUNTIF(A2,"*"&$E$2:$E$4&"*")), "Texas", "Not Texas")

The following screenshot shows how to use this formula in practice:

Notice that any row that contains Mavs, Spurs, or Rockets receives a value of Texas while all other rows receive a value of Not Texas.

The following tutorials explain how to perform other common tasks in Excel: