How to Match Data in Excel from 2 Worksheets

Trying to make sense of two different Excel spreadsheets with related information can feel like solving a puzzle with missing pieces. You know the data you need is split between Sheet1 and Sheet2, but getting it all into one cohesive view is the real challenge. This article will show you exactly how to match and combine data from two worksheets in Excel, starting with simple formulas and moving to more powerful, automated methods.

First Things First: Prepare Your Data for Success

Before you write a single formula, a little bit of prep work will save you from major headaches. For any of these methods to work, your two worksheets need one thing in common: a unique identifier. This is a column in both sheets that contains the exact same information, which Excel can use to match the rows correctly.

Common unique identifiers include:

• Email Address
• Customer ID
• Order Number
• SKU (Stock Keeping Unit)
• Invoice ID
• Employee ID

Once you've identified your common column, quickly check for these common data-cleaning issues:

• Extra Spaces: A simple space at the beginning or end of a cell can cause a mismatch. Use the =TRIM() function to remove them.
• Inconsistent Formatting: Make sure numbers are stored as numbers and text as text in both columns. 12345 is not the same as "12345" to Excel.
• Typos and Case Sensitivity: "Apple Inc." is different from "apple inc.". Use functions like =LOWER() or =UPPER() to make casing consistent across both sheets before matching.

Taking a few minutes to clean up your unique identifier columns will ensure your formulas and tools work accurately on the first try.

Method 1: The Classic VLOOKUP Formula

VLOOKUP (Vertical Lookup) is the traditional workhorse for matching data in Excel. It searches for a value in the first column of a table and returns a corresponding value from a specified column in the same row.

When to use VLOOKUP: It's perfect for straightforward situations where you need to pull data from a table, and your unique identifier is in the leftmost column of that table.

Let’s say you have two sheets:

• Sheet1 ("Orders"): Contains OrderID and Product. You want to pull the CustomerName into this sheet.
• Sheet2 ("Customer Info"): Contains OrderID, CustomerName, and ShippingCity.

Your goal is to fill in the CustomerName column in Sheet1 by matching OrderID.

Step-by-Step with VLOOKUP

1. Click on the cell in Sheet1 where you want the first customer name to appear (let’s assume it's cell C2).
2. Type the following formula:

Breaking Down the Formula:

• A2: This is the lookup_value. It's the unique identifier in your current sheet (OrderID 1001) that you want to find in the other sheet.
• 'Customer Info'!A:B: This is the table_array. It's the range of data on the other sheet where Excel should look. Critically, the first column of this range must be your unique identifier (OrderID). We've selected columns A (OrderID) and B (CustomerName).
• 2: This is the col_index_num. It tells Excel to return the value from the 2nd column of your table_array. In our A:B range, CustomerName is the second column.
• FALSE: This specifies an exact match. Always use FALSE when matching unique IDs to avoid incorrect results.

After you press Enter, Excel will find OrderID 1001 in Sheet2 and return the corresponding name, "Alice Johnson." You can then drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the rest of your orders.

Method 2: The More Flexible INDEX and MATCH Combo

While VLOOKUP is great, it has a famous limitation: it can only look to the right. The unique ID must be in the first column of your lookup table. This is where the powerful duo of INDEX and MATCH comes in.

When to use INDEX/MATCH: Use this combination when your lookup column isn't the first column in the data table, or if you want a more resilient formula that won't break if you insert or delete columns later.

Imagine your "Customer Info" sheet is structured differently, with CustomerName in column A and OrderID in column B. VLOOKUP can't handle this, but INDEX/MATCH can.

• INDEX returns the value of a cell in a table based on a row and column number.
• MATCH returns the position (row number) of a specific value in a range.

When used together, MATCH finds the row number of your OrderID, and INDEX uses that number to retrieve the CustomerName from the correct column.

Step-by-Step with INDEX/MATCH

1. In Sheet1, click on cell C2 where you want the customer name to appear.
2. Type this formula:

Breaking Down the Formula:

• INDEX('Customer Info'!A:A, ...): Tells Excel to return a value from column A in the "Customer Info" sheet (the list of customer names).
• MATCH(A2, 'Customer Info'!B:B, 0): Finds the row number where OrderID 1001 appears in column B of Sheet2.

This formula finds the row number where OrderID 1001 appears in column B of Sheet2 and then returns the value from that same row in column A. It’s more flexible, efficient, and a favorite among proficient Excel users.

Method 3: The Modern Solution - XLOOKUP (Microsoft 365)

If you have a modern version of Excel (Microsoft 365), XLOOKUP is the powerful and intuitive successor to both VLOOKUP and INDEX/MATCH.

When to use XLOOKUP: Honestly, use it whenever you can. It’s simpler to read, can look left or right, and has built-in error handling without needing extra functions.

Step-by-Step with XLOOKUP

Using the same scenario where we need to find the CustomerName in Sheet2 using the OrderID from Sheet1:

1. In Sheet1, click on cell C2.
2. Type this beautifully simple formula:

Breaking Down the Formula:

• A2: The lookup_value (the OrderID we're searching for).
• 'Customer Info'!A:A: The lookup_array. The column containing the OrderIDs in Sheet2.
• 'Customer Info'!B:B: The return_array. The column containing the CustomerNames we want to retrieve.
• "Not Found": Optional. If the OrderID doesn't exist, it returns "Not Found" instead of a #N/A error.

With XLOOKUP, you no longer need column numbers or nested functions. You simply select the lookup column and the return column. It's clean, efficient, and much harder to mess up.

Method 4: Handling Complex Data with Power Query

Formulas are great for one-off tasks, but what if you're working with tens of thousands of rows or need to repeat this matching process every week? This is where Power Query shines. It’s a data transformation engine built into modern versions of Excel.

When to use Power Query: Use it for large datasets, when you need to merge data based on multiple criteria, or when you want to build a reusable, refreshable report.

Step-by-Step with Power Query (Merge)

Let’s merge our "Orders" and "Customer Info" tables.

1. Load Both Tables into Power Query

• First, make sure both of your data ranges are formatted as official Excel Tables (select your data, go to the Insert tab, and click Table).
• Click anywhere in your first table (e.g., the Orders table in Sheet1).
• Go to the Data tab and click From Table/Range. This will open the Power Query Editor.
• In the Power Query Editor, click Close & Load To..., select "Only Create Connection," and click OK. Repeat for the second table ("Customer Info").

2. Merge the Queries

• Go back to the Data tab in Excel.
• Click Get Data > Combine Queries > Merge.
• Select your "Orders" table and your "Customer Info" table.
• Click on the OrderID column in both tables to match.
• For the "Join Kind," select Left Outer.
• Click OK.

3. Expand and Load the Data

• In the new query window, you'll see a table with a new column (from your second table) containing "Table".
• Click the expand icon (two arrows) on that column.
• Uncheck all except CustomerName. Uncheck "Use original column name as prefix" if desired.
• Click OK.
• Click Close & Load to insert the merged data into a new worksheet.

Anytime your source data updates, just refresh the table, and it will update automatically.

Final Thoughts

From the straightforward VLOOKUP to the resilient INDEX/MATCH, the modern XLOOKUP, and the automated Power Query, Excel provides a full toolkit for matching data between worksheets. Choosing the right method depends on your data's complexity, your version of Excel, and whether you need a one-time fix or an automated reporting solution.

As powerful as these tools are, they are often just one step in the broader process of creating meaningful reports. When you find yourself constantly exporting CSVs and cross-referencing spreadsheets just to keep up, it might be time for a more streamlined approach. At Graphed, we connect your data analysis tools, making it easy to create real-time dashboards by simply describing what you want to see - no more manual lookups. Instead of fighting formulas, you can focus on building visualizations instantly, updated as frequently as you like.