How to Match Data in Excel

Trying to compare two lists in Excel to see what matches can feel like a chore, but it’s a necessary one for anyone working with data. Whether you're comparing event registrants against your customer list or cross-referencing product IDs from a supplier with your own inventory, finding the overlap is a common task. This guide will walk you through four powerful and practical methods to match data in Excel, from the classic functions everyone should know to modern, more efficient alternatives.

Before You Start: A Common Scenario

To make these methods clear, let’s use a simple, relatable example. Imagine you have two lists:

List A (A2:A100): A list of emails of people who registered for your recent webinar.
List B (C2:D100): A two-column list of all your current customers, with their email in column C and their subscription plan in column D.

Your goal is to identify which webinar registrants are also existing customers and, ideally, see what plan they are on. This is a perfect job for Excel's lookup functions.

Method 1: The Classic Workhorse - VLOOKUP

VLOOKUP is arguably the most well-known lookup function in Excel. It stands for "Vertical Lookup" and is designed to search for a value in the first column of a table and return a corresponding value from a specified column in the same row.

How VLOOKUP Works

The syntax for VLOOKUP is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to look for. In our case, an email from the webinar list (e.g., cell A2).
table_array: The range of data where you want to search. This is our customer list (C2:D100). An important rule: The column you're searching in (customer emails) must be the first column in this range.
col_index_num: The column number in your table_array from which you want to return a value. Since our table_array is C2:D100, column C is 1 and column D (the plan) is 2. We want to return the plan, so we'll use 2.
range_lookup: This can be TRUE (approximate match) or FALSE (exact match). For matching unique identifiers like emails, you almost always want FALSE.

Step-by-Step Instructions:

Click on the cell next to your first webinar registrant's email (let’s say, cell B2).
Type the following formula:

=VLOOKUP(A2, $C$2:$D$100, 2, FALSE)

(Note the $ signs. These "lock" the range C2:D100, so it doesn't shift when you drag the formula down to other cells. You can add them by highlighting the range in the formula bar and pressing F4.)

Press Enter. Excel will search for the email from A2 in the customer list. If it finds a match, it will return the corresponding subscription plan. If not, it will display a #N/A error.
Click the small square (the fill handle) at the bottom right corner of cell B2 and drag it down to apply the formula to all your webinar registrants.

What you'll get is a brand new column showing the subscription plan for every registrant who is also a customer, making it easy to see the matches!

Pro Tip: Cleaning up #N/A Errors

Seeing a bunch of #N/A errors for non-matches isn't always clean. You can wrap your VLOOKUP formula in an IFERROR function to return something more descriptive.

=IFERROR(VLOOKUP(A2, $C$2:$D$100, 2, FALSE), "Not a Customer")

Now, any cell that previously showed an error will neatly say "Not a Customer."

Method 2: The Modern Upgrade - XLOOKUP

If you're using Microsoft 365 or Excel 2021, you have access to XLOOKUP. This function is a direct and far more flexible replacement for VLOOKUP. It overcomes many of VLOOKUP's limitations, making data matching much easier.

Why XLOOKUP is Better

No First Column Restriction: You can look up a value in any column and return a value from any other column, to the left or right.
Simpler Arguments: You select the lookup column and the return column separately, so you don't have to count column numbers.
Built-in Error Handling: It has an optional argument for what to return if no match is found, eliminating the need for IFERROR.
Safer Default: It defaults to an exact match, which is what you typically need.

Step-by-Step Instructions:

The syntax for a basic XLOOKUP is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Navigate to cell B2 next to our first webinar registrant.
Type the following formula:

=XLOOKUP(A2, $C$2:$C$100, $D$2:$D$100, "Not a Customer")

Let's break this down:

Lookup Email A2...
...in the customer email column ($C$2:$C$100)...
...and return the corresponding value from the plan column ($D$2:$D$100)...
...and if you can't find it, just say "Not a Customer".

Press Enter and drag the formula down. The result is the same as our IFERROR(VLOOKUP(...)) combination but with a cleaner, more intuitive formula.

Method 3: The Flexible Powerhouse - INDEX & MATCH

Before XLOOKUP existed, the combination of the INDEX and MATCH functions was the gold standard for advanced lookups. For users on older versions of Excel (like Excel 2019, 2016, etc.), this is still the most powerful and flexible method.

How INDEX & MATCH Work Together

Think of it as a two-step process:

MATCH finds the position: It looks for a value in a column and tells you its row number. For example, MATCH("jane.doe@email.com", C2:C100, 0) might return 15, meaning Jane's email is on the 15th row of that range. The 0 specifies an exact match.
INDEX gets the value: It retrieves the value from a specific row and column in a range. For example, INDEX(D2:D100, 15) would return whatever is in the 15th cell of the subscription plan column.

By nesting them, you get a dynamic lookup:

=INDEX(D2:D100, MATCH(A2, C2:C100, 0))

Step-by-Step Instructions:

In cell B2, type the nested formula:

=INDEX($D$2:$D$100, MATCH(A2, $C$2:$C$100, 0))

This tells Excel: "First, MATCH the email in A2 within the customer emails (C2:C100) to find its row number. Then, INDEX our plans column (D2:D100) and give me the value from that exact row."

Press Enter and drag the formula down.
Like VLOOKUP, this combo will return a #N/A error if there's no match, so you can also wrap it in IFERROR for a cleaner look.

=IFERROR(INDEX($D$2:$D$100, MATCH(A2, $C$2:$C$100, 0)), "Not a Customer")

Bonus Method: For a Simple "Yes/No" Match

Sometimes you don't need to return a corresponding value. You just want to know if an item in List A exists anywhere in List B. For this, the COUNTIF function is perfect.

How COUNTIF Works

COUNTIF counts the number of cells within a range that meet a specific condition. The syntax is dead simple:

=COUNTIF(range, criteria)

Step-by-Step Instructions:

Let's use a new column for this (e.g., cell C2). You want to see if the registrant in A2 is in the customer email list (C2:C100).
Type the following formula:

=COUNTIF($C$2:$C$100, A2)

Press Enter.

This formula will return a 1 if the email is found in the customer list and a 0 if it is not. While 1s and 0s work, you can make this even clearer by wrapping it in an IF statement.

=IF(COUNTIF($C$2:$C$100, A2) > 0, "Yes", "No")

This tells Excel: "If the count is greater than 0, write 'Yes', otherwise, write 'No'." Drag this down, and you have an elegant way to flag all your matches.

Which Method Should You Choose?

It can be tough to know which one is right for your task. Here’s a quick guide:

For the most modern, flexible, and easy solution: Use XLOOKUP. If you have it, this should be your default.
For backward compatibility or complex lookups: Use INDEX/MATCH. It offers the same flexibility as XLOOKUP and works in virtually all versions of Excel.
For quick and simple lookups where data is organized neatly: VLOOKUP still gets the job done and is widely understood.
For simply checking if a value exists in another list: COUNTIF is the fastest and most efficient tool for the job.

Final Thoughts

Matching data is one of the bedrocks of data analysis in Excel. Understanding how to use functions like VLOOKUP, XLOOKUP, INDEX/MATCH, and COUNTIF gives you a powerful toolkit to compare lists, enrich datasets, and find the insights hiding in your spreadsheets. Practice with each one, and soon they’ll become second nature.

While mastering these formulas is a great skill, this process of manually exporting lists and wrangling them in spreadsheets is precisely the kind of repetitive work we built Graphed to eliminate. Instead of spending hours matching CSV files, we allow you to connect your data sources (like your CRM, email platform, and event software) directly. From there, you can just ask in plain English, "Show me a list of attendees from the Q2 webinar who are on the Pro Plan," and get an answer instantly. If you find yourself in Excel more than you'd like, give Graphed a try and see a better way to get these answers automatically.