How to Join Tables in Excel (6 Suitable Methods)

Sometimes you may need to join tables in Excel for passing information. So, if you are looking for how to join tables in Excel then you have come to the right place. Today, I will demonstrate how to join tables in Excel.

Furthermore, for conducting the session, I will use Microsoft 365 version.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

6 Suitable Methods to Join Tables in Excel

Here, I will describe 6 suitable methods to join tables in Excel. In addition, for your better understanding, I’m going to use a sample dataset, which contains two tables. Additionally, one table contains the information related to products, and the other stores the information related to sellers. The dataset is given below.

Actually, you need to keep a common or identical column for both tables.

Furthermore, I have stored these tables in two different sheets too. The names of the sheets are Table 1 and Table 2.

1. Use of Excel VLOOKUP Function for Connecting Tables

Here, you can use the VLOOKUP function to join tables in Excel. Actually, with the help of this VLOOKUP function, you can connect not only tables from different sheets but also within a single sheet.

In this section, I will show you the joining between two tables from different sheets. Now, let’s start with the steps.

Steps:

• Firstly, I will give a name to the table. So, select any of the cells within the table.
• Secondly, from the Table Design tab >> write your preferable name in the Table Name box. Here, I have named the Product Info table as Product and the Seller Info table as Seller.

At this time, I will use another sheet named VLOOKUP Function to join these tables, where I have inserted the Product ID column.

• Now, let’s write the formula in the C5 cell using the VLOOKUP function in the sheet named VLOOKUP Function.

• Then, press ENTER to get the result.
• As a result, you will see all the sales from the table named Product.

• Similarly, write the following formula in the D5 cell.

Actually, this is exactly like the previous formula but here I have used the col_index_num as 3 for the Profit column.

• Subsequently, press ENTER to get the result.

• After that, write the following formula in the E5 cell.

Basically, this is also like the previous formula but here I have used the Seller table.

• Then, press ENTER to get the result.

• In the same way, write the following formula in the F5 cell.

• Lastly, press ENTER, and you will get the joined table.

Read More: How to Use Join Formula in Excel (6 Practical Examples)

2. Merge INDEX & MATCH Functions to Add Tables

Here, you can merge the INDEX and MATCH functions to join tables in Excel. Actually, with the help of these functions, you can join tables both from different sheets and within a single sheet.

Now, I will show you the joining between two tables within a single sheet. Basically, I have the product information and now I want to add the seller information with this from the Seller table. So, let’s start with the steps.

Steps:

• First, let’s write the formula in the E6 cell.

• Then, press ENTER.

• Similarly, write the formula in the F6 cell.

Here, the formula is exactly like the previous one but here I have used the Seller column for output.

• Consequently, press ENTER.

Lastly, you will get the table.

Read More: How to Join Names in Excel (7 Quick Methods)

3. Utilize Excel XLOOKUP Function for Joining Tables

Now, you can use the XLOOKUP function to join tables in Excel. Actually, with the help of these functions, you can join tables only within a single sheet. Here, I have the product information and now I want to add the seller information with this from the Seller table. So, let’s start with the steps.

Steps:

• Firstly, write the following formula in the E6 cell.

• Then, press ENTER.

• Again, write the formula in the F6 cell.

Here, the formula is similar to the previous one, but I have used the Seller ID column for output.

• After that, press ENTER.

Finally, you will get the table.

Read More: How to Perform Left Join in Excel (3 Easy Ways)

4. Use of Keyboard Shortcuts to Link Tables in Excel

Similarly, I can use a keyboard shortcut to link tables in Excel. Additionally, with the help of this method, you can connect not only tables from different sheets but also within a single sheet.

In this section, I will show you the joining between two tables from different sheets. For this, follow the steps below.

Steps:

• Firstly, select the table.
• Secondly, press CTRL+C to copy the values.

• Then, go to your target sheet or cell and press CTRL+V to paste the values.

As a result, you will see the table in different places.

• Similarly, copy the Seller table from the Table 2 sheet and paste it into the Keyboard Shortcuts sheet beside the product information.

Lastly, you will get your new table. But here if you want to change the sequence then you have to do it manually. Which means you have to copy-paste cell values one by one.

However, the advantage of this method is you don’t need any common column for joining.

5. Application of Power Query to Connect Tables

Here, you can apply Power Query to connect tables in Excel. The steps are given below.

Steps:

• Firstly, select the B5 cell.
• Secondly, go to the Data tab >> choose From Table/Range.

At this time, we will see the Power Query Editor. Here, if you got any Compatibility Warning then you can press the Close button to that warning, and then you will see the Power Query Editor.

• After that, from Close & Load >> select Close & Load To.

Thus, you will see another dialog box named Import Data.

• Then, check Only Create Connection.
• Subsequently, press OK.

As a result, you will see the Queries & Connections window at the rightmost corner of the worksheet, where you will get Table1 in Connection-only mode.

• Similarly, create connections for Seller Info.

• Now, go to the Data tab >> from Get Data >> choose Combine Queries >> select Merge.

At this time, you will see the dialog box named Merge.

• Then, from the 1st drop-down arrow >> choose Table1.

• Similarly, choose Table2 in the next box.
• Then, choose Left Outer (all from first, matching from second).

• Now, select the common column between the tables.
• Then, press OK.

Consequently, you will find the Merge1- Power Query Editor.

• Now, click on the Expand icon beside Table2.

• Then, from the pop-up dialog box >> uncheck Product ID.
• Then, uncheck Use original column name as prefix.
• Lastly, press OK.

So, you will see the expanded form of your table.

• After that, from Close & Load >> select Close & Load.

Lastly, you will get the joined table in a new sheet named Merge1.

Read More: How to Perform Outer Join in Excel (2 Easy Ways)

6. Use of Pivot Table in Excel

To join tables in Excel, you can use a Pivot table. So, let’s start with Pivot Table making.

Steps:

• Firstly, you may select any of the cells from the table. Here, I have selected the D4 cell.
• Secondly, from the Insert tab >> select the PivotTable feature >> then choose From Table/Range.

Subsequently, a dialog box of PivotTable from table or range will appear.

• Firstly, make sure that Product table is auto-selected in the Table/Range box.
• Secondly, choose New Worksheet.
• Thirdly, check Add this data to the Data Model.
• Finally, press Ok.

At this time, you will see the following situation where in the All menu, you will get all the tables of your workbook.

• Then, click any cell of the PivotTable1.

As a result, two contextual design tabs will be visible.

• Then, from the PivotTable Analyze >> select Relationships which are under Calculations group.

Consequently, a dialog box named Manage Relationships will appear.

• Then, click on New.

Eventually, another dialog box named Create Relationships will appear.

• Then, select your preferable Table, Column (Foreign), Related Table, and Related Column (Primary).
• After that, press OK.

• Now, in PivotTable Fields, you have to drag Product ID from Table 1 to Rows.

So, you will see the following output.

• Similarly, drag Seller, Seller ID to the Rows and Sales and Profit to the Values.

Finally, your joined PivotTable is done.

Read More: How to Join Pivot Tables in Excel (with Easy Steps)

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

I hope you found this article helpful. Here, I have explained 6 suitable methods to join tables in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.