Rubayed Razib Suprov
In the workspace, we frequently encounter the need of merging two separate tables with each other. Merging two separate tables will increase the clarity of the information and make interpretation easier. If you are curious to know how you can merge two tables in Excel, then this article may come in handy for you. In this article, we discuss how you can merge two separate tables in Excel with an elaborate explanation.
Download Practice Workbook
Download this practice workbook below.
5 Easy Ways to Merge Two Tables in Excel
We will use the following dataset in order to create a relationship between the two tables in Excel with duplicate values. Both of the data sets have a common column. The common column is the Product Id. The merging of the two tables will be based on this column.
1. Using VLOOKUP Function
The VLOOKUP function will help us to look for the value from one column to another column. And then copy that value, and paste it to the destination cell. Which eventually help us to merge two tables in Excel.
Steps
- In the below image, we got two tables that we want to merge together
- In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.
- To do this, first, select the cell I4 and enter the following formula:
=VLOOKUP(F4,$B$4:$D$10,2,FALSE)
- And then drag the Fill Handle to cell I10.
- Doing this will fill the range of cell I4:I10 with the first column of the first table, matching with the Product Id column.
- To add the second column, select cell J4 and enter the following formula:
=VLOOKUP(F4,$B$4:$D$10,3,FALSE)
- And then drag the Fill Handle to cell J10.
- Doing this will fill the range of cells J5:J10 with the first column of the first table, matching with the Product Id column.
- Now we need to format new columns to match the rest of the cells.
- Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
- A small paint brush appears in the place of the cursor.
- With that cursor, select the range of cells I4:J10.
- Finally, you can see the two tables are now merged and formatted.
Read More: How to Merge Two Tables in Excel Using VLOOKUP
2. Utilizing XLOOKUP Function
The XLOOKUP function will work in the almost similar way the previous method works. In XLOOKUP, the user needs to enter the output range argument that will be returned instead of the serial no of the column in a table.
Steps
- In the below image, we got two tables that we want to merge together
- In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.
- To do this, first, select the cell I4 and enter the following formula:
=XLOOKUP(F4,$B$4:$B$10,$C$4:$C$10)
- And then drag the Fill Handle to cell I10.
- Doing this will fill the range of cells I4:I10 with the first column of the first table, matching with the Product Id column.
- To add the second column, select cell J4 and enter the following formula:
=XLOOKUP(F4,$B$4:$B$10,$D$4:$D$10)
- And then drag the Fill Handle to cell J10.
- Doing this will fill the range of cells J4:J10 with the first column of the first table, matching with the Product Id column.
- Now we need to format new columns to match the rest of the cells.
- Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
- A small paint brush appears in the place of the cursor.
- With that cursor, select the range of cells I4:J10.
- Finally, you can see the two tables are now merged and formatted.
Read More: How to Merge Two Pivot Tables in Excel (with Quick Steps)
3. Applying Power Query
Power Query is one of the most powerful tools in Excel. Power query has many powerful default features that can solve many issues instantaneously
Steps
- In the below image, we got two tables that we want to merge together
- In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.
- To add these two data tables, go to Data > Get Data.
- Furthermore proceed to From Other Sources > From Table/Range.
- A small dialog box will appear.
- In that dialog box, you need to enter the range of the table and tick the My table has headers box.
- Click OK after this.
- For the second table, do the same thing and add the table to the Power Query.
- In the Power Query creates a table dialog box, specify the range of the table and tick the check box My table has headers.
- Click OK after this.
- After uploading both of the tables to the power query, we can merge them together using the combine feature.
- For this, open the power query editor (clicking OK in the previous step will automatically launch the editor).
- In the power query editor, go to the Home tab.
- And from the Home tab, go to the Combine group. And then click on the Merge Queries.
- From the drop-down menu, click on the Merge Queries.
- In the new window named Merge, choose Table 1 as the first table
- And in the second dropdown menu, choose Table 2 as the second table.
- In the below drop-down menu, choose Left Outer (all from the first, matching from the second).
- This dropdown option means that the matching will start with the first table from the left side, then the right side matching part will come eventually.
- Click OK after this.
- Right after pressing OK, you will see that one column named Table2 is appended with the first table.
- But this column is actually a shrunken version of Table2.
- All the columns are hidden in this Table2 column.
- To show all of the columns of Table2, click on the right top corner of the Table2 column header.
- Then in the expanded menu, only tick the Quantity and Cost check box, as we already have the Product Id in the first table.
- Uncheck the Use Original Column name as a prefix box.
- Click OK after this.
- After clicking OK, you will notice that the two columns are now added to the first table.
- Then click on Close and Load from the Home tab.
- Then from the drop-down menu, click on Close and Load To.
- And select Table in the Select how you want to view this data in your workbook
- Then choose the Existing worksheet and then select cell B13.
- Click OK after this.
- After this, the table will be loaded into cell B13:F19.
- And we can see that both of the tables are now merged.
Read More: How to Combine Two Tables Using Power Query in Excel
4. Combining INDEX and MATCH Functions
The INDEX and MATCH functions will help us merge the two tables in Excel by matching the value and then indexing the value from the second table to the first table.
Steps
- In the below image, we got two tables that we want to merge together
- In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.
- To do this, first, select the cell I4 and enter the following formula:
=INDEX($C$4:$C$10,MATCH($F4,$B$4:$B$10,0))
- And then drag the Fill Handle to cell I10.
- Doing this will fill the range of cells I5:I10 with the first column of the first table, matching with the Product Id column.
- To add the second column, select cell J4 and enter the following formula:
=INDEX($D$4:$D$10,MATCH($F4,$B$4:$B$10,0))
- And then drag the Fill Handle to cell J10.
- Doing this will fill the range of cells I4:I10 with the first column of the first table, matching with the Product Id column.
- This will complete the merging operation.
- Now we need to format new columns to match the rest of the cells.
- Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
- A small paint brush appears in the place of the cursor.
- With that cursor, select the range of cells I4:J10.
- Finally, you can see the two tables are now merged and formatted.
🔎 Formula Breakdown
- MATCH($F4,$B$4:$B$10,0)
This function will look for the exact value specified in the first argument in the array/ range of cells mentioned in the second argument. In this case, it will look for the value in the cell F4 in the lookup array in the B4:B10, and return the serial of that value in that range.
- INDEX($C$4:$C$10,MATCH($F4,$B$4:$B$10,0))
After we got the serial of the matched value in the lookup array, then using that serial, it will look for the value of the same serial in the other column (first argument) in the table.
Read More: How to Merge Two Tables in Excel with Common Column (5 Ways)
5. Use of Excel Copy-Paste Feature
Compared to the previous methods, this one is quite straightforward. We will directly copy and paste the columns of the second table to the first table.
Steps
- Like previous methods, we need to have two tables with a common column.
- At the same time, the common column values in both of the tables have to be in the same serial.
- First, select the second and the third column of the first column and then right-click on the mouse.
- From the context menu, click on the Copy.
- Then select cell I4 and right-click on the mouse again.
- From the context menu, click on Paste.
- Doing this will paste the table columns into the second table.
- Pasting the first table columns into the second table column will finally merge the two tables.
Read More: How to Merge Two Tables Based on One Column in Excel (3 Ways)
Things to Remember
- You need to maintain the same serial for the column entries in the common columns in both of the tables.
- In the Power Query method, do not select the second table in the first place. Always select the first table from which the matching will start in the first place.
Conclusion
To sum it up, the issue of how we can merge two separate tables in Excel in Excel with duplicate values is answered here in 5 different ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
