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.
Example: Check if Cell Contains Text from List in Excel
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Duplicates in Excel
How to Count Frequency of Text in Excel
How to Calculate Average If Cell Contains Text in Excel