Excel Trick: Find Mismatched Values From a Pair of Lists

 

In today’s world, data drives decisions, innovations, and solutions. We live in an era where trillions of data points are generated every day, flowing across various industries and domains. From sales and marketing to scientific research and beyond, analyzing and transforming this raw data into meaningful insights has become inevitable. Microsoft Excel stands out as one of the most versatile and accessible tools for handling data of all kinds. With its powerful features, Excel empowers users to perform zillions of tasks that might seem challenging or tedious to accomplish on other platforms.

In this article, we’ll demonstrate a powerful yet simple to accomplish feature of Excel to find mismatched values from two lists. We’ll put two methods to display the differences. First of all we need two sample lists having difference of some items as shown in the below screen-shot captured from Excel sheet.

As you can see, the column A, captioned by “Reg. # List One” contains first list and column C, captioned as “Reg. # List Two” represents seconds list.

Let’s move on to our first method.

 

Method 1

Assume that our data start from Row # 3. We want to find values from column C which are missing in A.

Click cell E3 and copy/paste or type-in the following formula in this cell.

=IF(ISNA(MATCH(C3, A:A, 0)), “Missing in A”, “”)

While the cell E3 is in focus, locate and grab the tiny square on the bottom-right of the highlighted cell and drag it down the rows up till the last cell of the lists.

This action will copy the formula to all cells in column E list. As soon as you’re done, you’ll find “Missing in A” text in some of the cell as shown in the picture below.

The “Missing in A” content are the indicators that reveal that values in the respective rows in column C are missing in column A.

Not to mention that you can do vice versa to find missing values in column C.

 

Method 2

This method is a better visual extension of Method 1. It uses nearly the same formula but reveals the missing value indicators through conditional formatting.

Select first data cell from Column C, that is C3. then go to the Home tab, and click Conditional Formatting > New Rule.

Choose Use a formula to determine which cells to format and enter:

=ISNA(MATCH(C3, A:A, 0))

Click Format, choose a fill color (e.g., red), and press OK.

You’re done with one cell. Now in order to apply the same formatting to all cells in the column C list.

Click C3, the first cell from the list on which you have just set the conditional formatting.
Click Home -> Format Painter icon from Clipboard section.
Now select cells from the 2nd cell all the way down to the last data value in the column.

You’ll immediately observe that those cells in the list will assume the selected format (as you can see in the image below) which have no match in the first list.

This is yet a better way than method 1 above since it visually reveals the difference, which is more eye-catching.