How to Combine Two Data Sets in Excel

Got two different Excel sheets with related information? Trying to merge them to get a complete picture is a classic data challenge. You might have sales transactions in one list and customer contact details in another, and you need to see them side-by-side. This article walks you through the best ways to combine two data sets in Excel, from tried-and-true methods to modern techniques that will save you time.

Before You Begin: The Common Column

To combine two sets of data, you need one piece of information that they both share. This is often called a unique identifier or a lookup key. Think of it as a mutual friend that connects your two lists.

This could be:

Email Address
Customer ID
Product SKU
Order Number
Employee ID

As long as you have one column in each data set containing the same type of information, you can link them together. For our examples, we will use two tables: a Sales Data table and a Customer Data table, both linked by a CustomerID column.

Method 1: The Classic Approach with VLOOKUP

If you've spent any time in Excel, you've probably heard of VLOOKUP. It stands for "Vertical Lookup," and 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 great for quick, straightforward lookups when you need to pull data from another table. It's a classic for a reason.

How to Use VLOOKUP

Let's say we want to add the Customer Name from our Customer Data table to our Sales Data table. We'll add a new column in the Sales sheet called "Customer Name".

Select the first empty cell in your new column. In our example, it's cell D2 in the "Sales Data" sheet.
Enter the VLOOKUP formula:

=VLOOKUP(A2, 'Customer Data'!$A$2:$C$6, 2, FALSE)

Let's break down what's happening here:

A2: This is the value we're looking up - the CustomerID from our Sales Data table.
'Customer Data'!$A$2:$C$6: This is the table we are searching within. Notice the $ signs, which "lock" the range so it doesn't shift when you drag the formula down. ! separates the sheet name from the cell range.
2: This is the column number in the Customer Data table from which to pull the information. The Customer Name is in the second column of that table.
FALSE: This tells Excel to look for an exact match. You'll want to use this almost every single time.

Press Enter. The name corresponding to CustomerID CA-12580 should appear.
Drag the formula down. Click the small square (the fill handle) at the bottom-right corner of cell D2 and drag it down to apply the formula to the rest of your sales data.

VLOOKUP Considerations:

The column you use for the lookup must be the first column in your lookup table (the table_array).
It can feel a bit clunky and is prone to errors if someone adds or removes columns from the source table.

Method 2: The Modern Upgrade with XLOOKUP

XLOOKUP is the modern, more powerful successor to VLOOKUP. If you have Microsoft 365 or a recent version of Excel, this is the function you should use. It’s more flexible, more straightforward, and less likely to break.

When to use XLOOKUP: Pretty much all the time. It replaces VLOOKUP and HLOOKUP and is the best function for most lookups.

How to Use XLOOKUP

Using the same example, let's pull the customer's State into our Sales Data sheet. We create a new column, "State," in our main table.

Click into the first cell where you want the State to appear (cell E2 in our example).
Enter the XLOOKUP formula:

=XLOOKUP(A2, 'Customer Data'!$A$2:$A$6, 'Customer Data'!$C$2:$C$6)

Here’s the breakdown:

A2: Again, this is our lookup value (the CustomerID).
'Customer Data'!$A$2:$A$6: This is the lookup array - the single column where Excel should look for the CustomerID.
'Customer Data'!$C$2:$C$6: This is the return array - the single column where Excel can find the State.

Why XLOOKUP is Better

Direction doesn't matter: Unlike VLOOKUP, your lookup column doesn't have to be on the far left. You can look up a value and return something to its left.
Less fragile: Since you specify the return column directly instead of by number, your formula won't break if someone inserts a new column.
Simpler: It finds an exact match by default - no need to add a FALSE at the end.

Method 3: The Repeatable Powerhouse, Power Query

For large datasets or recurring reports, a formula-based approach can slow down your workbook and be a pain to manage. Power Query (also known as "Get & Transform Data" in recent Excel versions) is a tool designed specifically for these situations.

When to use Power Query: When your data is large, needs cleaning up, comes from multiple sources, or the combination is a task you repeat frequently (e.g., weekly reporting).

How to Use Power Query

This method doesn't use formulas in cells. Instead, it creates a repeatable process that merges your data behind the scenes.

Format your data as tables. Select each of your data sets and press Ctrl + T (or go to Insert > Table). Name your tables something memorable, like "SalesData" and "CustomerData".
Load both tables into Power Query.
- Select a cell in your first table (SalesData).
- Go to the Data tab and click From Table/Range.
- The Power Query Editor window will open. Your data looks good, so click Close & Load To....
- In the pop-up, choose "Only Create Connection" and click OK.
- Repeat this entire process for your second table (CustomerData). Now you have connections to both tables in your workbook.
Merge the queries.
- Go to the Data tab, click Get Data > Combine Queries > Merge.
- In the Merge window, select "SalesData" from the first dropdown and "CustomerData" from the second.
- Click the CustomerID column header in both tables to tell Power Query this is your common column.
- The Join Kind should default to "Left Outer," which works like a VLOOKUP (it keeps all rows from the first table and adds matching ones from the second). This is what you want. Click OK.
Expand your combined data.
- The Power Query editor opens again with your new merged table. You'll see a column named after your second table ("CustomerData") with the word "Table" in each cell.
- Click the expand icon (two arrows pointing in opposite directions) on that column's header.
- Unselect the columns you don't need to bring over (like the duplicate CustomerID) and click OK. Your data is now joined!
Load the new table. Click the Close & Load button in the top left. Power Query will create a new worksheet containing your finalized, merged table.

The best part? If your raw data changes, just right-click your final merged table and select Refresh. The table will update instantly with all the new information.

Which Method is Right for You?

The best method depends entirely on your needs:

For a quick, one-off analysis: XLOOKUP is your best friend. It's fast, flexible, and powerful. If you're using an older version of Excel, VLOOKUP gets the job done.
For repeatable reports or large datasets: Power Query is the clear winner. It automates the process, handles huge amounts of data efficiently, and ensures your process is consistent every time.

Final Thoughts

Combining data in Excel doesn't have to be complicated. Whether you choose the flexibility of XLOOKUP or the repeatable power of Power Query, having the right technique makes all the difference. Knowing how to link information from different sources is a fundamental skill for anyone digging into data.

Combining separate CSVs on your computer is one puzzle, but the complexity skyrockets when your data is fragmented across different platforms entirely - like pairing Facebook Ads campaign costs with Shopify sales data, or Google Analytics sessions with HubSpot leads. Instead of manually exporting CSV files every week just to wrangle them in a spreadsheet, we built Graphed to do this automatically. It connects directly to your marketing and sales platforms, so you can just ask in plain English: "Create a report showing Facebook Ads spend, sessions, and Shopify revenue by campaign for last month." All your scattered data comes together in a live, refreshable dashboard in seconds.