“Merging” two tables in Excel can mean two different things. You might want to stack them, putting the rows of one table under the other because they have the same columns, such as January and February sales. Or you might want to join them side by side, pulling columns from one table into another by matching a shared value, such as an order ID or employee number. This guide covers both, using copy and paste, formulas, and Power Query.
For the examples, imagine Table1 has Order ID, Customer, and Amount, and Table2 has Order ID and Ship Date. You want one table with all four columns.
Method 1: Stack Two Tables with Copy and Paste
According to Microsoft’s guide to merging tables, the simplest way to combine rows is to paste them right below an existing table:
- Make sure both tables have the same columns in the same order.
- Select the data rows of the second table (without its headers) and press Ctrl + C.
- Click the first empty cell directly under the first table and press Ctrl + V.
If the first range is an Excel table, it expands automatically to include the new rows. This is a one-time copy, so later changes to the second table won’t show up.
Method 2: Stack Tables with VSTACK
In Excel for Microsoft 365 and Excel 2024, enter =VSTACK(Table1,Table2) in an empty cell on a new sheet. The result spills down and updates automatically when either table changes. Add headers by including them once: =VSTACK(Table1[#Headers],Table1,Table2).
Method 3: Join Tables by a Matching Column with XLOOKUP
To add Ship Date from Table2 to Table1 by matching Order ID:
- Add a new column to Table1 called Ship Date.
- In its first cell, enter =XLOOKUP([@[Order ID]],Table2[Order ID],Table2[Ship Date],”Not found”).
- Press Enter. In an Excel table, the formula fills down automatically. In a normal range, use something like =XLOOKUP(A2,Sheet2!$A$2:$A$500,Sheet2!$B$2:$B$500,”Not found”) and drag it down.
- Format the column as a date.
XLOOKUP is available in Excel for Microsoft 365, Excel 2021, and later. The last argument shows “Not found” instead of an error when an Order ID has no match.
Method 4: Join Tables with VLOOKUP (Older Excel Versions)
In Excel 2019 and earlier, use VLOOKUP. Its syntax is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE):
- Click the first cell of the new Ship Date column in the first table.
- Enter =VLOOKUP(A2,Sheet2!$A$2:$B$500,2,FALSE), where A2 is the Order ID, the range is Table2, and 2 means the second column of that range.
- Drag the formula down to fill the column.
VLOOKUP needs the matching column to be the first column of the lookup range, and FALSE is required for an exact match. Wrap it in IFERROR to hide missing matches: =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$500,2,FALSE),””).
Method 5: Merge Tables with Power Query
Power Query is the best choice for large tables, tables in different files, or merges you’ll repeat every month.
- Click inside Table1 and go to Data > From Table/Range. In the Power Query Editor, choose Close & Load To > Only Create Connection.
- Do the same for Table2.
- Go to Data > Get Data > Combine Queries > Merge.
- Pick Table1 in the top box and Table2 in the bottom box, and click the Order ID column in each.
- Choose a Join Kind. Left Outer keeps every row from Table1 and adds matching data from Table2.
- Click OK. In the new column, click the expand button, check Ship Date, and click OK.
- Click Close & Load to put the merged table on a new sheet.
When the source tables change, click Data > Refresh All to update the merged table. To stack tables instead, use Combine Queries > Append.
Which Method Should You Use?
- Same columns, one-time job: copy and paste.
- Same columns, needs to stay updated: VSTACK or Power Query Append.
- Adding a few columns by ID: XLOOKUP, or VLOOKUP in older versions.
- Large data, several files, or a repeat task: Power Query Merge.
Tips for Merging Two Tables in Excel
- Make sure the matching column has unique values, such as an ID. Lookups return only the first match when values repeat.
- Convert both ranges to Excel tables with Ctrl + T. Table names make formulas easier to read and ranges grow automatically.
- Back up the workbook before you paste or replace data.
- After a formula-based merge, copy the new column and use Paste Special > Values if you need to delete the second table.
Troubleshooting
Lookups Return #N/A for Values That Look the Same
The IDs probably don’t match exactly. Common causes are extra spaces (fix with =TRIM()), numbers stored as text in one table and as numbers in the other, or hidden characters such as non-breaking spaces. Make both columns the same type, then try again.
VLOOKUP Returns the Wrong Column
The column number counts from the first column of the lookup range, not from column A of the sheet. Recount, or switch to XLOOKUP, which points to the return column directly.
Rows Are Duplicated After a Power Query Merge
The second table has more than one row for the same ID, so Power Query returns every match. Remove duplicates from the second table first, or choose the specific columns you need.
Pasted Rows Don’t Become Part of the Table
Paste in the row directly below the table, with no blank row in between, so the table expands to include them.
Frequently Asked Questions
How do I handle missing data?
Use XLOOKUP’s if_not_found argument, or wrap VLOOKUP in IFERROR, to show a blank or a message instead of #N/A.
Can I merge tables with different column names?
Yes. Lookups and Power Query only need a column with matching values. The headers don’t have to match. For stacking, rename the columns so they line up first.
What if my tables are in different files?
Open both workbooks and point the lookup range at the other file, or use Power Query’s Get Data > From File > From Workbook to load each file, then merge or append them.
How do I update the merged table?
Formulas update automatically. Power Query results update when you click Refresh All. Copied data never updates.
Can I merge more than two tables?
Yes. VSTACK and Power Query Append accept several tables at once. For lookups, add one column at a time from each table.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.