How to Compare Two Data Sets in Excel for Matches

Working with two separate lists in Excel and trying to find what they have in common is a classic data task. Whether you're comparing an inventory list against a sales report or subscriber lists from two different campaigns, figuring out the overlap is often the first step to finding key insights. This tutorial will walk you through several effective methods to compare two data sets in Excel for matches, from simple formulas to powerful, built-in tools for handling massive amounts of data.

First Things First: Prepare Your Data

Before you start comparing, a little prep work can save you a lot of headaches. Inaccurate matches often happen because of small inconsistencies in the data. Here are a couple of quick cleanup steps:

• Remove Extra Spaces: A common culprit for failed matches is a leading or trailing space. You can use the TRIM function to clean your data. Simply create a new column and enter the formula =TRIM(A2), then drag it down for your whole list.
• Check Case Sensitivity: By default, most Excel lookup functions are case-insensitive ("Apple" will match with "apple"). However, if case matters to you, be aware that you might need to use more advanced formulas, like those combining EXACT and INDEX-MATCH. For most business use cases, case-insensitivity is fine.
• Ensure Consistent Formatting: Make sure numbers are stored as numbers and text as text on both lists, especially for ID numbers or SKUs that might accidentally be treated differently by Excel.

Method 1: Using VLOOKUP to Find Matches

VLOOKUP (Vertical Lookup) is the trusty, go-to function for most Excel users when it comes to finding matches. Its job is to search for a value in the first column of a table and return a corresponding value from another column in the same row. We can use it to see if a value from List 1 exists anywhere in List 2.

Let's say you have two lists: List1 (in Column A) is your master product list, and List2 (in Column D) is a list of products sold this month. You want to mark which products from your master list were sold.

You’ll create a new column, let's call it "Sold," next to your master list.

The VLOOKUP Formula and How It Works

In cell B2, you would type the following formula:

=VLOOKUP(A2, D:D, 1, FALSE)

Let’s break that down:

• A2 (lookup_value): This is the cell you want to find a match for. In our case, it’s the first product ID in List1.
• D:D (table_array): This is the range where you're looking for the match. We're telling Excel to search the entire Column D (List2).
• 1 (col_index_num): If a match is found, this is the column number from the table_array whose value you want returned. Since our table_array is only one column (D:D), we use 1.
• FALSE (range_lookup): This is crucial. Using FALSE tells VLOOKUP to find an exact match. If you omit it or use TRUE, it will look for an approximate match, which can lead to incorrect results in this scenario.

Step-by-Step Instructions:

1. Click on cell B2 (the first cell in your new "Sold" column).
2. Enter the formula: =VLOOKUP(A2, D:D, 1, FALSE)
3. Press Enter. Excel will search for the value in A2 within Column D. If it finds a match, it will return that value. If it doesn't find a match, you'll see a #N/A error.
4. Click on the small square at the bottom-right corner of cell B2 and drag it down to apply the formula to your entire master list.

The #N/A error is actually helpful here - it clearly identifies the products from your master list that are not found in the sales list. To make the sheet cleaner, you can wrap your VLOOKUP formula in an IFNA function:

=IFNA(VLOOKUP(A2, D:D, 1, FALSE), "No Match")

This formula does the same thing, but instead of showing #N/A, it will display the friendlier text "No Match."

Method 2: A Simpler Approach with COUNTIF

If you don't need to return a value from the second list and simply want to know if a match exists, the COUNTIF function is an easier and faster option. It counts how many times a specific value appears in a range.

Using the same product list example, let’s find out which products in List1 (Column A) appear in List2 (Column D).

The COUNTIF Formula

In a new column next to List1, enter this formula in cell B2:

=COUNTIF(D:D, A2)

• D:D (range): This is the range where you want to count a value, in this case, our List2.
• A2 (criteria): This is the value you want to count, which is the product ID from List1.

When you drag this formula down, it will return a number. If the number is 0, it means there's no match. If the number is 1 (or higher), it means a match exists. To make this even clearer, you can wrap it in an IF statement:

=IF(COUNTIF(D:D, A2) > 0, "Match Found", "No Match")

This formula gives you a clean confirmation, which is perfect for quick checks and audits.

Method 3: Highlighting Matches with Conditional Formatting

Sometimes you don't want to add extra columns with formulas. You just want to see the matches or differences visually. Conditional formatting is perfect for this.

Let's highlight every cell in List1 that also exists in List2.

Step-by-Step Instructions:

1. Select the entire range of your first list (e.g., A2:A100).
2. Go to the Home tab on the ribbon, click on Conditional Formatting, and then select New Rule...
3. In the window that appears, choose "Use a formula to determine which cells to format."
4. In the formula box, enter the COUNTIF formula we used before, relative to the first cell in your selection (A2):

=COUNTIF($D:$D, A2)>0

Notice the $ signs on $D:$D. This is an absolute reference, which ensures that as conditional formatting checks each cell in your selection (A3, A4, etc.), it always refers to the entire column D. A2 is a relative reference, allowing it to adapt to each cell being checked.

1. Click the Format... button.
2. Go to the Fill tab and choose a color to highlight your matching cells (like a light green).
3. Click OK twice to close the windows.

Instantly, all the products in List1 that also appear in List2 will be highlighted in the color you chose. This is a fast and effective way to spot overlaps without altering your data structure.

Method 4: The Best Way for Large Datasets - Power Query (Get & Transform)

If you're working with thousands - or hundreds of thousands - of rows, formulas like VLOOKUP can make your Excel file slow to a crawl. For large datasets, the most efficient and robust tool is Power Query. It’s built into modern versions of Excel (Data > Get & Transform Data).

Power Query lets you connect to your data lists, compare them, and load only the results you want back into an Excel sheet. The best part is that this process is repeatable. Once set up, you can refresh it with one click if your source data changes.

Here’s how to find matches between List1 and List2 using Power Query:

Step-by-Step Instructions:

1. Load Your Lists into Power Query:
2. Merge the Queries to Find Matches:
3. Finalize and Load:

Power Query may seem complicated at first, but it is by far the most powerful and scalable solution for comparing data in Excel. It automates your entire comparison process, handles millions of rows without a problem, and is a must-know tool for anyone serious about Excel data analysis.

Final Thoughts

Comparing data sets in Excel is a fundamental skill, and knowing multiple ways to do it equips you for any situation. Whether you need a simple visual check with conditional formatting, a quick answer from a VLOOKUP or COUNTIF formula, or a robust, automated comparison for large datasets using Power Query, Excel has a tool that fits your needs.

While Excel is incredibly versatile for these tasks, this kind of manual comparison across different exports speaks to a larger challenge: bringing all your data together in one place. At Graphed, we built a way to eliminate the need for CSV downloads and manual VLOOKUPS altogether. By directly connecting your tools like Shopify, Google Analytics, Salesforce, and Facebook Ads, we let you ask questions about performance across all of them in simple English. You can create a real-time dashboard comparing data from two entirely different platforms just by describing what you want to see, no formulas required.