Looking for ways to know how to **merge two tables in Exce**l 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**.

**Table of Contents**hide

## 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)}**

- 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 Oute**r 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)**