lookup-between-two-lists-of-data-to-highlight-missing-data1To the right is a picture of 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 in A2:A6:
=COUNTIF(List2, $A2)=0

The conditional formatting formula in B2:B6:
=COUNTIF(List1, $B2)=0

lookup-between-two-lists-of-data-to-highlight-missing-data

Named ranges
List1 (A2:A6)
List2 (B2:B6)
What is named ranges?

How to customize the formula to your excel workbook
Change the named ranges and change the cell reference  $A2 in formula =COUNTIF(List2, $A2)=0 to the starting point of your list

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

Functions used in this blog post:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition