Explanation
This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function to return a check mark (✓) when a logical test returns TRUE:
=IF(logical_test,"✓","")
If the test returns FALSE, the formula returns an empty string (""). For the logical test, we are using the COUNTIF function like this:
=COUNTIF(list,B5)
COUNTIF returns a count of how many times the value in B5 occurs in the named range list (E5:E9). If the value in B5 exists in the range E5:E9, COUNTIF will return 1. If not, COUNTIF will return zero. Excel’s standard behavior is to evaluate any non-zero number as TRUE, and zero as FALSE. So, If the value in B5 exists in E5:E9, COUNTIF returns 1 and IF returns a check mark (✓). If the value in B5 is not found in the allowed list, COUNTIF returns zero and IF returns an empty string (""), which displays nothing.
With hardcoded values
The example above shows allowed values in a range of cells, but allowed values can also be hardcoded into a slightly more complex version of the formula as an array constant like this:
=IF(SUM(COUNTIF(B5,{"red","blue","green"})),"✓","")
Notice that we need to provide the array constant as the criteria , and B5 as the range . This is because COUNTIF will not accept an array constant as the range argument. Because of this change, COUNTIF will return 3 counts (one for each value in the array constant) and we also need to wrap the SUM function around COUNTIF to catch the results from COUNTIF and return a final count.
Check mark character (✓)
Inserting a checkmark character in Excel can be surprisingly challenging and you will find many articles on the internet explaining various approaches. The easiest way to get the check mark character (✓) used in this formula into Excel is simply to copy and paste it. If you are copying from this web page, paste it directly into the formula bar to avoid bringing in unwanted formatting. You can also copy and paste directly from the attached worksheet.
If you have trouble with copy and paste, try using the UNICHAR function to insert a checkmark like this:
=UNICHAR(10003) // returns "✓"
UNICHAR (10003) returns a Unicode version of the checkmark: Unicode 2713 (U+2713) . The original formula can be written like this:
=IF(COUNTIF(list,B5),UNICHAR(10003),"")
Note: the UNICHAR function was introduced in Excel 2013.
Extending the formula
The basic idea in this formula can be extended in many clever ways. For example, the screenshot below shows a formula that returns a check mark only when all test scores are at least 65:
The formula in G5 is:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
The NOT function reverses the result from COUNTIF. If you find this confusing, you can alternately restructure the IF formula like this:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
In the version of the formula, the logic is more similar to the original formula above. However, we have moved the check mark to the value_if_false argument, so the check mark will appear only if the count from COUNTIF is zero. In other words, the check mark will appear only when no values less than 65 are found.
Note: you can also use conditional formatting to highlight valid or invalid input, and data validation to restrict input to allow only valid data.
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.
