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)
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 tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two [โฆ]
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 [โฆ]
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 [โฆ]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column [โฆ]
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.
English
Spanish
