Exceljet

Quick, clean, and to the point

Value exists in a range

Excel formula: Value exists in a range
Generic formula 
=COUNTIF(range,value)>0
Explanation 

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

where "rng" is the named range F4:F10.

How this formula works

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")

This works because IF considers any number > 0 to as TRUE when it appears as the logical test argument.

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.