You can use the following formulas to check if a range in Excel contains a specific value:
Method 1: Check if Range Contains Value (Return TRUE or FALSE)
=COUNTIF(A1:A10,"this_value")>0
Method 2: Check if Range Contains Partial Value (Return TRUE or FALSE)
=COUNTIF(A1:A10,"*this_val*")>0
Method 3: Check if Range Contains Value (Return Custom Text)
=IF(COUNTIF(A1:A10,"this_value"),"Yes","No")
The following examples show how to use each formula in practice with the following dataset in Excel:
Example 1: Check if Range Contains Value (Return TRUE or FALSE)
We can use the following formula to check if the range of team names contains the value “Mavericks”:
=COUNTIF(A2:A15,"Mavericks")>0
The following screenshot shows how to use this formula in practice:
The formula returns FALSE since the value “Mavericks” does not exist in the range A2:A15.
Example 2: Check if Range Contains Partial Value (Return TRUE or FALSE)
We can use the following formula to check if the range of team names contains the partial value “avs” in any cell:
=COUNTIF(A2:A15,"*avs*")>0
The following screenshot shows how to use this formula in practice:
The formula returns TRUE since the partial value “avs” occurs in at least one cell in the range A2:A15.
Example 3: Check if Range Contains Value (Return Custom Text)
We can use the following formula to check if the range of team names contains the value “Hornets” in any cell and return either “Yes” or “No” as a result:
=IF(COUNTIF(A2:A15,"Hornets"),"Yes","No")
The following screenshot shows how to use this formula in practice:
The formula returns No since the value “Hornets” does not occur in any cell in the range A2:A15.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel
