## Lookup between two lists of data to highlight missing data using conditional formatting in excel

To 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

**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 fil****e**

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

Highlight dates within a date range using conditional formatting

Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a […]Create a dynamic border to your list using excel conditional formatting

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]