Last updated on November 9th, 2026 at 01:18 pm
“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
The simplest way to combine rows is to paste them right below an existing table (the same approach appears in Microsoft’s guide to merging tables):
- 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.
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.
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.
#N/A and wrong columns when merging
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.

Matt Jacobs writes and maintains the guides on Support Your Tech, which he started in 2017. He spent most of his career keeping small-business PCs running and still takes the occasional consulting job, so the steps here come from fixing the same problems on other people’s computers. Every guide is checked on his own Windows 11, Windows 10, Mac, iPhone and iPad before it goes up.