Author: Oscar Cronquist Article last updated on November 18, 2018

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? And vice versa? To do this I use conditional formatting.

To extract a list of missing values using excel formula, check this blog post What values are missing in List 1 that exists i List 2?

The conditional formatting formula applied to B3:B7:

=COUNTIF($D$3:$D$7, B3)=0

The conditional formatting formula applied to D3:D7:

=COUNTIF($B$3:$B$7, D3)=0

Explaining CF formula applied to B3:B7

The COUNTIF function counts the number of time the value in cell B3 exists in cell range D3:D7.

Cell COUNTIF Result
B3 COUNTIF($B$3:$B$7, B3) 0
B4 COUNTIF($B$3:$B$7, B4) 1
B5 COUNTIF($B$3:$B$7, B5) 1

Now if the COUNTIF function returns 0 (zero) that means that the value is not found in the other cell range and the logical expression returns TRUE.

COUNTIF($D$3:$D$7, B3)=0

becomes

0=0

and returns TRUE.

Cell B3 is highlighted, the value is not found in the other list.

Get excel example file
lookup-between-two-lists-of-data-to-highlight-missing-data.xls
(Excel 97-2003 Workbook *.xls)

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Compare two columns and highlight values in common

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

Compare two columns and highlight differences

This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]