Range contains specific value

If you need to determine if a value exists in a range of cells, you can use a simple formula based on the COUNTIF function.

In the example shown, the formula in D5 is:

=COUNTIF(rng,B5)>0

The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero. Any positive number means the value was found. This means you can use the greater than operator (>) to run a simple test on the result of COUNTIF to force a TRUE or FALSE result:

=COUNTIF(range,value)>0

You can also wrap the formula inside an IF statement to force a specific result. For example, to return “Yes” or “No”,  use:

=IF(COUNTIF(range,value),"Yes","No")

Testing for a substring or partial match

If you want to test a range to see if it contains specific text (a substring or partial text), you can add wildcards to the formula. For example, if you have a value to look for in cell C1, and you want to check the range A1:A100, you can configure COUNTIF to look for that value anywhere in a cell by concatenating asterisks:

=COUNTIF(A1:A100,"*"&C1&"*")>0

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the  value in C1, the formula will count the substring in C1 anywhere it appears in each cell of the range.

An alternative formula using MATCH

As an alternative, you can use a formula that uses the MATCH function instead of COUNTIF:

=ISNUMBER(MATCH(value,range,0))

The MATCH function returns the position of a match (as a number) if found, and #N/A if not found. By wrapping MATCH inside ISNUMBER, the final result will be TRUE when MATCH finds a match and FALSE when MATCH returns #N/A.