Arin Islam
Looking for ways to know how to merge two tables in Excel with a common column? Sometimes, we want to merge two tables that have a common column. Here, you will find 5 ways to merge two tables in Excel with a common column.
Download Practice Workbook
5 Ways to Merge Two Tables in Excel with Common Column
Here, we have two tables containing information of some students having a common column named Student ID. Now, we will use these tables to show you how to merge two tables in Excel with a common column.
1. Applying Copy-Paste Feature to Merge Two Tables with Common Column in Excel
In the first method, we will show you how to apply Copy-Paste Feature to merge two tables in Excel with a common column. This method can only be used if common column values in both of the tables are in the same serial. Follow the steps given below to do it on your own dataset.
Steps:
- Firstly, select Cell range C4:C11.
- Then, click on the Copy button.
- After that, select Cell G4 and Right-click on it.
- Next, click on the Paste Option.
- Finally, data from two tables will be merged in Excel with a common column by applying Copy-Paste Feature.
Read More: How to Merge Two Tables Based on One Column in Excel (3 Ways)
2. Using VLOOKUP Function to Merge Two Tables with Common Column
Now, we will show you how to merge two tables with a common column in Excel using the VLOOKUP Function. The VLOOKUP function is used to look for a given value in a specific array and then to return a value from the same array from a specific column.
Go through the steps given below to do it on your own.
Steps:
- In the beginning, select Cell G4.
- After that, insert the following formula.
=VLOOKUP(B4,B4:C11,2,FALSE)
Here, in the VLOOKUP function, we inserted Cell B4 as lookup_value, Cell range B4:C11 as table_array, 2 as col_index_number, and FALSE as range_lookup. Based on the value of B4 it will return the value of C4 which is Height. Here Student ID column is the common column.
- Then, press ENTER.
- Next, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get the values of Height from the other table matching the data from the common column Student ID.
- Then, to copy the format of the given table, select Cell range C4:C11.
- After that, click on the Format Painter button.
- Next, click on Cell G4.
- Finally, data from two tables will be merged in Excel with a common column using the VLOOKUP Function.
Read More: How to Merge Two Tables in Excel Using VLOOKUP
3. Use of XLOOKUP Function to Merge Two Tables with Common Column in Excel
In the third method, we will show you how to merge two tables with a common column in Excel using the XLOOKUP Function. The XLOOKUP function is used to look for a given value in a specific array and then to return a value from the given column.
Follow the steps given below to do it on your own.
Steps:
- Firstly, select Cell G4.
- After that, insert the following formula.
=XLOOKUP(E4,B4:B11,C4:C11)
Here, in the XLOOKUP function, we inserted Cell B4 as lookup_value, Cell range B4:C11 as lookup_array, and C4:C11 as return_array.
- Then, press ENTER.
- Now, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Next, you will get the values of Height from the other table matching the data from the common column Student ID.
- Then, follow the steps shown in Method 2 to change the format of Cell range G4:G11 according to the other columns of the table.
- Finally, you will get the merged table in Excel with a common column using the XLOOKUP Function.
4. Merging Two Tables with Common Column by Applying INDEX and MATCH Functions
Next, we will show you how to merge two tables with a common column in Excel using the INDEX and MATCH Functions. Go through the steps given below to do it on your own dataset.
Steps:
- In the beginning, select Cell G4.
- After that, insert the following formula.
=INDEX($C$4:$C$11,MATCH($E4,$B$4:$B$11,0))
Formula Breakdown
- MATCH($E4,$B$4:$B$11,0)—–> The MATCH function returns the relative position of data in a specific given array in order.
- Output: {1}
- INDEX($C$4:$C$11,MATCH($E4,$B$4:$B$11,0))—–> The INDEX function returns a value of the given reference in an array.
- INDEX($C$4:$C$11,1)—–> turns into
- Output: {Height(cm)}
- INDEX($C$4:$C$11,1)—–> turns into
- Then, press ENTER.
- Next, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get the values of Height from the other table matching the data from common column Student ID.
- Then, follow the steps shown in Method 2 to change the format of Cell range G4:G11 according to the other columns of the table.
- Finally, you will get the merged table in Excel with a common column using the INDEX and MATCH Functions.
Read More: How to Merge Two Pivot Tables in Excel (with Quick Steps)
5. Using Power Query to Merge Two Tables with Common Column
In the final method, we will show you how to merge two tables with a common column in Excel using Power Query.
Follow the steps given below to do it on your own dataset.
Steps:
- Firstly, go to the Data tab >> click on Get Data >> click on From Other Sources >> select From Table/Range.
- Now, the Create Table box will open.
- Then, insert Cell range B4:C11 in the box.
- Next, turn on My table has header option.
- After that, click on OK.
- Now, Table1 will be added in Power Query Editor.
- Next, change the name of the table to Height from Query Settings.
- Now, the Import Data box will appear.
- After that, select Only Create Connection.
- Then, click on OK.
- Similarly, open the Create Table box for the second table going through the steps given above.
- Next, insert Cell range E4:F11 in the box.
- Then, turn on My table has header option.
- After that, click on OK.
- Now, Table2 will be added in Power Query Editor.
- Next, change the name of the table to Name from Query Settings.
- Then, create a connection with this table by going through the steps given above.
- After that, go to the Data tab >> click on Get Data >> click on Combine Queries >> select Merge.
- Now, the Merge box will appear.
- After that, select the Name table and Student ID column in it.
- Next, select the Height table and Student ID column in it.
- Then, select Left Outer as Join Kind.
- Finally, click on OK.
- Now, the Merge1 table will appear in Power Query Editor.
- Then, click on the box shown below.
- After that, click on Height.
- Next, turn off the Use original column name as prefix option.
- Then, click on OK.
- Now, click on Close & Load >>select Close & Load To.
- Next, the Import Data box will appear.
- Then, select Existing worksheet.
- Now, insert Cell B13 in the box.
- After that, click on OK.
- Finally, you will get the merged table in Excel with a common column using Power Query.
Read More: How to Combine Two Tables Using Power Query in Excel
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Conclusion
So, in this article, you will find 5 ways to merge two tables in Excel with a common column. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!
Related Articles
- How to Merge Two Tables in Excel and Remove Duplicates
- How to Merge Tables from Different Sheets in Excel (5 Easy Ways)
