How to Merge Tables in Excel (All You Should Know)

This article explores the basic thing of how to merge tables in Excel, which is essential for efficiently combining data.

Merging tables means putting together pieces of information from different places to make it easier to study and make important decisions with the data. It helps us see all the data in one place, so we can understand it better and use it to our advantage.

Power Query tool, VLOOKUP, INDEX&MATCH, and XLOOKUP functions are used in the examples of this article to combine two tables from different or the same worksheet.

All the steps of combining tables are mentioned with proper illustrations to make you understand the procedure. You can also download the worksheet to practice your learning hand-in-hand.

We have used Microsoft 365 while writing this article. But all the methods are also applicable to any versions pf Excel.

Download Practice Workbook

How to Merge Tables in Excel?

You can merge table using Power Query, various types of functions like VLOOKUP, INDEX&MATCH, and XLOOKUP. We can easily combine two tables in the same worksheet.

Merge Tables in Excel Using Power Query

We will combine two tables from multiple worksheets with the Power Query Tool. We will be combining the marks of Physics from two different sections of classes A and B. Below, there are two different tables of Physics-A and Physics-B.

To use the  Power Query tool, follow the steps ahead.

• From the Data tab, click on the Get Data
• Then, from the drop-down menu, click on From Other Sources.
• Now, click on Blank Query.

Power Query Editor will appear as follows.

• There, in the formula bar, paste the code below

• Then, click on the Double Arrow as shown in the picture below.
• A dialogue box will appear, there check Select All Columns.
• Finally, press OK.

• A combined dataset will appear in the Power Query Editor. Now, just simply click on Close & Load.

• Finally, a combined data table will appear in the new worksheet named

How to Merge Two Tables by One Column with VLOOKUP?

Here, we have two tables named Table-1: Employee Credentials, and Table-2:Salary Data in two separate worksheets. Here, we will be using the VLOOKUP function to combine these two tables into one by one common column.

• Then, in cell E5, use the formula. And press Ctrl+Shift+Enter.

• Rename the E column as Salary. The merged table will appear as follows.

How to Join Tables with INDEX MATCH Functions?

In the two examples below, we will use the INDEX&MATCH function to combine two tables from different worksheets or the same worksheet.

Merge Tables in Excel by Matching Single Column

The datasets Table-1: Employee Credentials and Table-2:Salary Data are presented in the same worksheet. We will combine these two.

• Write down the formula and press Ctrl+Shift+Enter.

• The Salary column will merge with Table-1: Employee Credentials.

Merge Tables in Excel by Matching Multiple Columns

The datasets Table-1: Employee Credentials and Table-2:Salary Data are presented in the same worksheet. We will combine these two into one table by matching two columns Employee Name and Department from Table-2.

• Use the formula below and press Ctrl+Shift+Enter.

• Salaries from Table-2 will be added to the Salary column of Table-1.

How to Merge Tables Utilizing XLOOKUP Function?

If you are using Excel 365, we have the XLOOKUP function,  a more powerful tool to combine two tables into one.

We will be combining with two tables the use of the XLOOKUP function that has a common column Employee Name. Follow the easy steps to learn the process.

• In cell E5, use the formula and press Enter.

Things to Remember

• Check data accuracy and organization.
• Identify a common field/column.
• Sort both tables based on the common field/column.
• Back up your data.
• Choose the appropriate merge method (e.g., VLOOKUP, INDEX-MATCH).

Frequently Asked Questions (FAQ)

1. How do I merge tables in Excel without Power Query?

• Ensure both tables have a common column with unique identifiers.
• Insert a new column in the table where you want to merge the data.
• Use the VLOOKUP function with the unique identifier as the lookup value, specifying the range of cells in the other table and the column number containing the data you want to retrieve.
• Drag the formula down to apply it to the rest of the cells.
• Double-check and adjust the formula references as needed.

By following these steps, you can merge two tables in Excel using the VLOOKUP function.

2. How do I merge 3 tables in Excel using Vlookup?

• Make sure all three tables have a column with unique identifiers.
• Insert new columns in the table where you want to merge the data. You’ll need one new column for each additional table.
• Use VLOOKUP in the first new column: In the first new column, use the VLOOKUP function to fetch data from the second table. Specify the unique identifier in the current row, the range of cells in the second table, and the column number containing the desired data.

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

• Use VLOOKUP in the second new column: In the next new column, use the VLOOKUP function to fetch data from the third table, following the same steps as above.
• Drag the formulas down: Apply the VLOOKUP formulas to the rest of the cells in the new columns.
• Adjust the formulas: Double-check and adjust the formula references as needed. The unique identifier should match the current row, and the table references should include the entire range of cells in the respective tables.

Conclusion

Hopefully, you have understood the use of Power Query, VLOOKUP, INDEX&MATCH, and XLOOKUP to merge columns of two tables in the article. All the steps are explained in an easy way.

If you encounter any problems or have any trouble with the formula, make sure to comment below. Our expert team member will get to you as soon as possible.

Merge Tables in Excel: Knowledge Hub

• Merge Two Tables in Excel and Remove Duplicates
• Merge Two Tables Based on One Column
• Merge Tables From Different Sheets
• Merge Two Tables in Excel Using Vlookup
• Merge Two Tables in Excel

<< Go Back to Merge in Excel | Learn Excel