Highlight missing values between to columns
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:
The conditional formatting formula applied to D3:D7:
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.
Download 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
A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
What values are missing in List 1 that exists i List 2?
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]
Identify missing numbers in a range
Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
