How to Merge Two Tables in Excel with Common Column (5 Ways)

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.

Ways to Merge Two Tables in Excel with 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.

Applying Copy-Paste Feature to Merge Two Tables with Common Column in Excel

  • 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)

Using VLOOKUP Function to Merge Two Tables with Common Column

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.

Using Format Painter to Copy Format to Merge Two Tables in Excel with Common Column

  • Next, click on Cell G4.

  • Finally, data from two tables will be merged in Excel with a common column using the VLOOKUP Function.

Merging Two Tables with Common Column Using VLOOKUP Function in Excel

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)

Use of XLOOKUP Function to Merge Two Tables with Common Column in Excel

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.

Merging Two Tables with Common Column Using XLOOKUP Function in Excel


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))

Merging Two Tables with Common Column by Applying INDEX and MATCH Functions

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)}
  • 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.

Merging Two Tables with Common Column Using INDEX and MATCH Functions in Excel

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.

Using Power Query to Merge Two Tables with Common Column

  • 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.

Opening Create Table box to Merge Two Tables in Excel with Common Column

  • Now, Table1 will be added in Power Query Editor.

  • Next, change the name of the table to Height from Query Settings.

  • Then, click on Close & Load >> select Close & Load to.

  • Now, the Import Data box will appear.
  • After that, select Only Create Connection.
  • Then, click on OK.

Opening Import Data Box to Merge Two Tables in Excel with Common Column

  • 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.

Opening Merge Box to Merge Two Tables in Excel with Common Column

  • 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.

Opening Import Data box for Merge Table to Merge Two Tables in Excel with Common Column

  • Finally, you will get the merged table in Excel with a common column using Power Query.

Merging Two Tables with Common Column Using Power Query in Excel

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.

Practice Section


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

 

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo