You can use the following formula to check if a cell in Excel contains one of several specific values:
=SUMPRODUCT(--ISNUMBER(SEARCH($E$2:$E$4,A2)))>0
This particular example checks if cell A2 contains any of the values in the range E2:E4.
If it does, then the formula returns TRUE.
Otherwise, the formula returns FALSE.
The following example shows how to use this formula in practice.
Example: Check if Cell Contains One of Several Values
Suppose we have the following dataset that contains information about points scored by various basketball players:
Suppose we would like to check if each cell in column A contains either the name Mavs, Jazz or Nets.
We can use the following formula to do so:
=SUMPRODUCT(--ISNUMBER(SEARCH($E$2:$E$4,A2)))>0
We’ll type this formula into cell C2 and then click and drag it down to each remaining cell in column C:
If a cell in column A contains one of the three teams of interest, then the corresponding cell in column C returns TRUE.
Otherwise, the cell in column C returns FALSE.
If you would like to return values other than TRUE or FALSE, you can wrap the formula in an IF function:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($E$2:$E$4,A2)))>0, "Yes", "No")
We’ll type this formula into cell C2 and then click and drag it down to every remaining cell in column C:
Each value in column C now returns “Yes” or “No” to indicate if the value in the corresponding cell in column A contains one of the three teams of interest.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Check if Range Contains Specific Value
Excel: How to Check if Cell Contains Partial Text
Excel: How to Check if One Column Value Exists in Another Column
