Musiha Mahfuza Mukta
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.
=VLOOKUP([@[Product ID]],Product[#All],2,FALSE)
Formula Breakdown
- Here, I have provided [@[Product ID]] as the lookup_value within the VLOOKUP function, and Product[#All] is the lookup_array. You can notice that I have used the table name.
- Then, 2 as the col_index_num because sales are in the 2nd column of the Product table.
- Lastly, FALSE for the exact match.
- 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.
=VLOOKUP([@[Product ID]],Product[#All],3,FALSE)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.
=VLOOKUP([@[Product ID]],Seller[#All],2,FALSE)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.
=VLOOKUP([@[Product ID]],Seller[#All],3,FALSE)- 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.
=INDEX(Seller_Info[Seller ID], MATCH([@[Product ID]], Seller_Info[Product ID], 0))- Then, press ENTER.
Formula Breakdown
- Here, within the MATCH function, [@[Product ID]] is the lookup_value, and Seller_Info[Product ID] is the lookup_range.
- Actually, I have used the table name. Where the table that holds the information of the seller is called Seller_Info.
- Then, 0 means the exact match.
- After that, this MATCH portion provides the position, and then INDEX pulls the value from Seller_Info[Seller ID] range.
- Similarly, write the formula in the F6 cell.
=INDEX(Seller_Info[Seller], MATCH([@[Product ID]], Seller_Info[Product ID], 0))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.
=XLOOKUP([@[Product ID]], Seller_Info17[Product ID], Seller_Info17[Seller])- Then, press ENTER.
Formula Breakdown
- Here, I have provided [@[Product ID]] as the lookup_value within the VLOOKUP function, and Seller_Info17[Product ID] as the lookup_array. You can notice that I have used the table name.
- Then, Seller_Info17[Seller] is the return_array.
- Where, I have used the table name. Furthermore, the table that holds the information of the seller is named Seller_Info17.
- Again, write the formula in the F6 cell.
=XLOOKUP([@[Product ID]], Seller_Info17[Product ID], Seller_Info17[Seller ID])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.
