How to Cross-Match Data in Excel

Trying to find matching data between two different lists in Excel can feel like looking for a needle in a haystack. Whether you're matching a list of event attendees against your customer CRM or comparing product SKUs from a sales report to your inventory master list, this is a common task that often turns into a manual headache. This article will show you several practical methods to cross-match data in Excel, from simple formula-based approaches to more powerful, automated techniques.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Understanding Cross-Matching in Excel

At its core, cross-matching (or data reconciliation) is about comparing two sets of data to find what they have in common. Imagine you have two guest lists for a party and you want to know who is on both lists to avoid sending duplicate invitations. That’s cross-matching.

In a business context, common scenarios include:

• Finding which customers from a new leads list already exist in your main database.
• Matching sales data with marketing campaign contacts to measure campaign effectiveness.
• Reconciling financial statements by comparing vendor invoice numbers with your payment records.
• Checking which products in your catalog are also listed in a distributor’s inventory file.

Excel offers multiple ways to tackle this, each with its own strengths. Let’s walk through the most effective methods, starting with the simplest.

Method 1: The VLOOKUP Function for Simple Matches

The VLOOKUP (Vertical Lookup) function is many people’s go-to for finding data. It searches for a specific value in the first column of a table and returns a corresponding value from a different column in the same row. We can use it to quickly check if a value from one list exists in another.

Let's say you have List A (email contacts from a recent webinar) in column A and List B (your master customer list) in column C.

Step-by-Step Instructions:

1. Click into the cell next to your first item in List A (e.g., cell B2).
2. Type the following formula:
3. Press Enter and drag the fill handle down to apply the formula to the rest of List A.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Let's break down this formula:

• A2: This is the value you're looking for (the first email from your webinar list).
• C:C: this is the range where you're searching (the entire master customer list in column C).
• 1: This tells VLOOKUP to return the value from the 1st column of the range we specified (which is just column C itself).
• FALSE: This is crucial. It commands VLOOKUP to find an exact match.

Interpreting the Results

When you run the formula, you will see one of two outcomes:

• A matching value: If the email from List A is found in List B, the formula will return that email.
• A #N/A error: This error means "Not Available." It tells you that the email from List A was not found in List B.

While the #N/A errors work, they're not very elegant. You can clean this up to make your results easier to read.

Tidying Up Your VLOOKUP with IFERROR

You can wrap your VLOOKUP formula inside an IFERROR function to return a more user-friendly message. This makes it easier to filter for matches or non-matches.

=IFERROR(VLOOKUP(A2, C:C, 1, FALSE), "No Match")

Now, instead of a harsh #N/A, any email not found on the master list will simply show "No Match." You can also make it return "Match" and "" (blank) for easier readability:

=IF(ISNA(VLOOKUP(A2, C:C, 1, FALSE)), "", "Match")

Method 2: Using INDEX and MATCH for Flexible Cross-Matching

While VLOOKUP is handy, it has a major limitation: it can only search for a value in the leftmost column of the lookup range. This is where INDEX and MATCH come in. Combining these two functions creates a more flexible and powerful lookup that can find data in a column in any direction - left or right.

MATCH finds the position (the row number) of a lookup value in a range.

INDEX returns the value at a specific position in a range.

Together, you use MATCH to find the row number of your item, and then you feed that number into INDEX to retrieve the corresponding value from another column.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Instructions:

Suppose you have product SKUs in column A and their inventory levels in column B. On another sheet, you have a sales report with those same SKUs in column E. You want to see the inventory level for each sold product.

1. In your sales report, next to the first SKU (in cell F2), type the MATCH part of the formula:
2. Now, use INDEX to grab the inventory number from that row in column B:

This formula tells Excel to find the position of the SKU in cell E2 inside column A, then return the value from that same position in column B. Just like VLOOKUP, you can wrap it in IFERROR to handle any SKUs that don't match.

Why is this better? If you later insert a column to the left of your lookup data, VLOOKUP will break, but INDEX/MATCH will continue to work perfectly. It's more resilient and a best practice for complex spreadsheets.

Method 3: Conditional Formatting to Quickly Highlight Matches

Sometimes you don't need to return a value — you just want to quickly see which items match. Conditional Formatting is the perfect tool for this. It allows you to format cells based on a rule, such as highlighting all values from List A that also exist in List B.

Step-by-Step Instructions:

1. Select the data in your first list (e.g., A2:A100).
2. On the Home tab, click Conditional Formatting > New Rule.
3. In the dialog box, select "Use a formula to determine which cells to format."
4. In the formula bar, enter this formula (assuming your second list is in C2:C150):
5. Click the "Format..." button and choose how you want to highlight the matching cells (e.g., a light green fill).
6. Click OK.

Excel will instantly highlight every cell in List A that has a matching entry in List B. This formula checks each cell in your selection (starting with A2) and counts how many times it appears in the second list ($C$2:$C$150). If the count is greater than zero, it applies the formatting. It's a fantastic way to get a quick visual overview of your overlapping data.

Method 4: Power Query for Advanced Merging

If you're dealing with large datasets (tens of thousands of rows or more) or need to do this matching repeatedly, formulas can be slow and cumbersome. Power Query (called Get & Transform Data in newer Excel versions) is an industrial-strength tool built right into Excel for this exact purpose.

Instead of single-cell formulas, Power Query lets you "merge" two tables based on a common column, creating a brand new, clean table with the results. It's a refreshable, automated process.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Instructions:

1. Format as Tables: First, convert both of your data ranges into official Excel Tables. Click anywhere in your first list and press Ctrl + T. Give the table a descriptive name (e.g., "WebinarLeads"). Do the same for your second list (e.g., "CustomerList").
2. Load to Power Query: Click on your first table, go to the Data tab, and click From Table/Range. This opens the Power Query Editor. Click "Close & Load" but choose "Only Create Connection." Repeat for the second table. Now both tables are available to Power Query.
3. Merge the Queries: Go back to the Data tab and click Get Data > Combine Queries > Merge.
4. Configure the Merge: A dialog box will appear.
5. Finalize and Load: Click OK. A new query will appear with the merged result. You'll see a new column containing the data from the second table. Click the expand icon on that column to choose which fields to display. When ready, click Close & Load to drop your final, merged table onto a new worksheet.

The beauty of Power Query is that it's a one-time setup. If your source data ever changes, you can simply right-click your output table and hit "Refresh." The entire merge operation runs again automatically, giving you up-to-date results in seconds.

Final Thoughts

As you can see, Excel provides a complete toolkit for cross-matching data, catering to simple and complex needs alike. Whether you're using a quick VLOOKUP, a flexible INDEX/MATCH duo, visual aid from Conditional Formatting, or the powerful automation of Power Query, you can say goodbye to manually hunting for matches in your spreadsheets.

While mastering these functions is valuable, we know that pulling data into spreadsheets is often just the beginning of a lengthy manual reporting process. Hours are spent exporting CSVs from different platforms like Google Analytics, Shopify, and Salesforce, then cleaning and matching them in Excel just to see a complete picture. With Graphed, we automate that entire workflow. You can connect your marketing and sales data sources with a few clicks, and instead of wrestling with formulas, simply ask for the report you need in plain English. Graphed automatically joins the data from different systems in real-time to build a live dashboard, letting you focus on the insights instead of the manual data prep.