Note: with conditional formatting, it’s important to enter the formula relative to the “active cell” in the selection, which is assumed to be A1 in this case. The key to this formula is the =0 at the end, which “flips” the logic of the formula. For each value in A1:A10, COUNTIF returns the number of times the value appears in C1:C10. As long as the value appears at least once in C1:C10, COUNTIF will return a non-zero number and the formula will return FALSE. But when a value is not found in C1:C10, the COUNTIF returns zero and, since 0 = 0, the formula will return TRUE and the conditional formatting will be applied.
Named ranges for simple syntax
If you name the list you are searching (C1:C10 in this case) with a named range, the formula is simpler to read and understand: This works because named ranges are automatically absolute.
Case-sensitive version
If you need a case sensitive count, you can use a formula like this: The EXACT function performs a case-sensitive evaluation and SUMPRODUCT tallies the result. As with the COUNTIF, this formula will return when the result is zero. Because the test is case-sensitive, “apple” will show as missing even if “Apple” or “APPLE” appears in the second list. See this page for a more detailed explanation.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
