Explanation
In this example, the goal is to use a formula to check if a specific value exists in a range. The easiest way to do this is to use the COUNTIF function to count occurrences of a value in a range, then use the count to create a final result.
COUNTIF function
The COUNTIF function counts cells that meet the supplied criteria. The generic syntax looks like this:
=COUNTIF(range,criteria)
Range is the range of cells to test, and criteria is a condition that should be tested. COUNTIF returns the number of cells in range that meet the condition defined by criteria . If no cells meet criteria , COUNTIF returns zero. In the example shown, we can use COUNTIF to count the values we are looking for like this
COUNTIF(data,E5)
Once the named range data (B5:B16) and cell E5 have been evaluated, we have:
=COUNTIF(data,E5)
=COUNTIF(B5:B16,"Blue")
=1
COUNTIF returns 1 because “Blue” occurs in the range B5:B16 once. Next, we use the greater than operator (>) to run a simple test to force a TRUE or FALSE result:
=COUNTIF(data,B5)>0 // returns TRUE or FALSE
By itself, the formula above will return TRUE or FALSE. The last part of the problem is to return a “Yes” or “No” result. To handle this, we nest the formula above into the IF function like this:
=IF(COUNTIF(data,E5)>0,"Yes","No")
This is the formula shown in the worksheet above. As the formula is copied down, COUNTIF returns a count of the value in column E. If the count is greater than zero, the IF function returns “Yes”. If the count is zero, IF returns “No”.
Slightly abbreviated
It is possible to shorten this formula slightly and get the same result like this:
=IF(COUNTIF(data,E5),"Yes","No")
Here, we have removed the “>0” test. Instead, we simply return the count to IF as the logical_test . This works because Excel will treat any non-zero number as TRUE when the number is evaluated as a Boolean .
Testing for a partial match
To test a range to see if it contains a substring (a partial match), you can add a wildcard 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 for partial matches, you can configure COUNTIF to look for the value in C1 anywhere in a cell by concatenating asterisks on both sides:
=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 text in C1 anywhere it appears in each cell in the range. To return “Yes” or “No”, nest the formula inside the IF function as above.
An alternative formula using MATCH
As an alternative, you can use a formula that uses the MATCH function with the ISNUMBER 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.
Explanation
At the core, this formula runs two tests on a value like this:
=D5>MIN(B5,C5) // is D5 greater than smaller?
=D5<MAX(B5,C5)) // is D5 less than larger?
In the first expression, the value is compared to the smaller of the two numbers, determined by the MIN function.
In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function.
The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number.
Include boundaries
To include the boundary numbers (num1 and num2), adjust the logical operators like this:
=AND(D5>=MIN(B5,C5),D5<=MAX(B5,C5))
Now the AND function will return TRUE only when the value is greater than or equal to the smaller number and less than or equal to the larger number.
Simple version
The formula in the example is more complex because there is no assumption that num1 is less than num2 (or vice versa). If it’s safe to assume that num1 is less than num2 , the formula can be simplified like this:
=AND(value>num1,val<num2)
