Explanation
This formula is evaluated for each of the 10 cells in A1:D10.
As with the COUNTIF, this formula will return when the result is zero.
Seethis pagefor a more detailed explanation.
Highlight missing values
To compare lists and highlight values that exist in one but not the other, you can apply conditional formatting with a formula based on the COUNTIF function. For example, to highlight values A1:A10 that don't exist C1:C10, select A1:A10 and create a conditional formatting rule based on this formula: =COUNTIF($C$1:$C$10,A1)=0 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.
