Explanation
In this example, the goal is to highlight all values in K4:M7 ( values ) that appear in the range B4:I15 ( data ). The range K4:M7 is named “values” for readability and convenience only. If you don’t want to use a named range, use an absolute reference instead.
Although this is a difficult problem for the human eye, it is exactly the kind of thing Excel does very well. The solution above shows matching data in green, and matching values in blue. The colors are applied automatically with conditional formatting and will update instantly if values change. This requires two separate conditional formatting rules, each with its own formula.
Highlight matching data (green)
This formula is based on the COUNTIF function , which takes two arguments: range and criteria and returns a count of values that match criteria. Normally, range represents the cells being checked. For example, to count cells in the range A1:A10 that are equal to 7, you would configure COUNTIF like this:
=COUNTIF(A1:A10,7) // count values = 7
However, in this case, we want to check each cell in B4:I15 against the 12 separate values in K4:M7. One option is to use COUNTIF with values as criteria and use SUM to add up results:
=SUM(COUNTIF(B4,values)) // returns {0,0,0;0,0,1;0,0,0;0,0,0}
Note: the rule is applied to all cells in B4:I15. The relative reference to B4 will change at each cell.
Because we are giving COUNTIF 12 separate numbers to evaluate as criteria, COUNTIF will return 12 counts in an array for each cell in the data. To force a single result, we wrap COUNTIF in the SUM function . Any sum greater than zero means B4 contains a number in values , and any non-zero result will evaluate as TRUE and trigger the rule.
However, a simpler option is to reverse the COUNTIF configuration like this:
=COUNTIF(values,B4) // returns 1
Here, range is values , and B4 is criteria . This gives us a single numeric result – the count of the cell value (B4) in values . As above, any non-zero result means that B4 is a number in K4:M7. And any non-zero result triggers the conditional formatting rule and colors the cell.
Highlight matching values (blue)
The above rule highlights numbers in data that appear in values . We can easily make a rule to highlight cells in values that contain numbers in data . In the example shown, the blue highlighting is created with the following formula:
=COUNTIF(data,K4) // blue highlighting
Notice the approach is the same as above. Each cell in values K4:M7 becomes the criteria argument inside COUNTIF while the range is data (B4:I15). As above, COUNTIF returns a single count for each cell in values and any non-zero result triggers the rule.
Count all matching values
To count the total number of cells in data (B4:I15) that match a number in values (K4:M7) you can use a formula like this:
=SUMPRODUCT(COUNTIF(values,data)) // returns 12
To count the total number of cells in values (K4:M7) that match a value in data (B4:I15), you can use a formula like this:
=SUMPRODUCT(--(COUNTIF(data,values)>0)) // returns 6
Here, because the numbers in values often appear more than once in data , we need to force non-zero counts from COUNTIF to 1 before we add them up, to avoid over counting. We do this by checking if the counts are greater than zero and using a double negative (–) to force the TRUE FALSE results to 1s and 0s.
Note: you can also use the SUM function in place of SUMPRODUCT above, but you will need to enter the formula as an array formula with control + shift + enter, unless you are using Excel 365 .
Explanation
This formula is evaluated for each of the 10 cells in A1:D10. A1 will change to the address of the cell being evaluated, while C1:C10 is entered as an absolute address, so it won’t change at all.
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:
=COUNTIF(list,A1)=0
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:
=SUMPRODUCT((--EXACT(A1,list)))=0
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.
