Exceljet

Quick, clean, and to the point

Validate input with check mark

Excel formula: Validate input with check mark
Generic formula 
=IF(logical_test,"P","")
Summary 

To display a check mark if a value is "allowed" based on an existing list of allowable values, you can use a formula based on the IF function together with the COUNTIF function. In the example show, the formula in C5 is:

=IF(COUNTIF(allowed,B5),"✓","")

where allowed is the named range E5:E9.

Explanation 

This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function set up 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(allowed,B5)

COUNTIF counts occurrences of the value in B5 in named range allowed (E5:E9). This may seem "backwards" to you, but if you think about it, it makes sense. If the value in B5 is found in the allowed list of values , COUNTIF will return a positive number (in this case 1). If not, COUNTIF will return zero. Excel will evaluate any non-zero number as TRUE, so this works perfectly as the logical test for IF.

IF will only return TRUE if the value is found in the allowed list, and, if so, the final result is a check mark (✓). If the value is not found in the allowed list, COUNTIF returns zero, which is evaluated as FALSE. In that case, the final result is an empty string (""), which displays nothing.

With fixed values

The example above shows allowed values in a range of cells, but allowed values can also be hard-coded into the formulas as an array constant like this:

=IF(COUNTIF({"red","blue","green"},B5),"✓","")

Check mark character (✓)

Inserting a check mark 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 into the formula bar to avoid dragging in unwanted formatting. You can also copy and paste directly from the attached worksheet.

If you have trouble with copy and paste, try this variation. The character itself is Unicode 2713 (U+2713), and can also be entered in Excel with the UNICHAR function like this:

=UNICHAR(10003) // returns "✓"

So, the original formula can be written like this:

=IF(COUNTIF(allowed,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:

Check mark if all values 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.

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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.