In this article, we will learn to merge two tables based on one column in Excel. Sometimes, you may have two or more tables and need to merge them to extract data. To merge the tables, we need to have a common column in both tables. Today, we will demonstrate 3 easy ways. Here, we will show the use of some formulas, Excel Power Query, or Copy-Paste options. Using these methods, you can easily merge two tables based on one column in Excel. So, without further delay, letâ€™s start the discussion.

To explain the methods, we will use two tables. Suppose, we have two sheets named Table 1 and Table 2. In the first sheet, we have a table that contains the sellerâ€™s info.

In the second sheet, we have another table that contains the sales amount. We will merge these two tables based on the Seller column. Here, the order of the sellers is not the same as in the first table.

After merging, the merged table will look like the dataset below.

## 1. Merging Two Tables Based on One Column Using Formula in Excel

In the first method, we will show two formulas that you can use to merge two tables based on one column in Excel. We will use the **VLOOKUP **function to build the first formula. In the second formula, we will use the combination of the **INDEX **and **MATCH **functions.

### 1.1. Apply VLOOKUP Function

To merge tables, we can use the **VLOOKUP **function. The **VLOOKUP **function looks for data in the leftmost column of a table and returns the matched information from the same row.

Letâ€™s follow the steps below to see how we can get the result.

**STEPS:**

- First of all, go to the first table and select
**Cell D5**. - Secondly, type the formula below in
**Cell D5**:

`=VLOOKUP(B5:B10,'Table 2'!B5:C10,2,FALSE)`

In this formula, we have four arguments inside the **VLOOKUP **function.

**Range B5:B10**is the lookup value. That means the formula will look for these values in**Table 2**.- The second argument denotes the table array of the
**Table 2**sheet. After typing the first argument, you need to type a comma (**,**) and move to the sheet that contains the second table. Then, select the table array from there. - The third argument denotes the second column of
**Table 2**and**FALSE**denotes the exact match.

- After that, press
**Ctrl**+**Shift**+**Enter**to get the results.

**Note: **As **VLOOKUP **looks for the value in the leftmost column of a table, it can produce errors and return incorrect results. It is also unable to extract data from a column left to the lookup column. So, it is wise to use other functions instead.

### 1.2. Insert INDEX-MATCH Combination

We can also use the combination of the **INDEX**–**MATCH **functions to merge two tables in Excel. The general form of the **INDEX**–**MATCH **can be written as:

`=INDEX(return_range, MATCH(lookup_value, lookup_range,0))`

Here, we will show the process to merge tables from different sheets and also from the same sheet. So, letâ€™s follow the steps below to see how we can implement this formula to merge two tables.

**STEPS:**

- In the first place, we show the process to merge tables from different sheets.
- To do so, select
**Cell D5**and type the formula below:

`=INDEX('Table 2'!$C$5:$C$10,MATCH('INDEX-MATCH'!$B5,'Table 2'!$B$5:$B$10,0))`

- After that, hit
**Enter**to see the result.

In this formula, we have merged two tables from the sheets named **Table 2 **and **INDEX**–**MATCH**.

**Table 2′!$C$5:$C$10:**This is the return range. The sheet named**Table 2**contains this range of the second table.**‘INDEX-MATCH’!$B5:**It is the lookup value. The sheet named**INDEX**–**MATCH**contains this value.**‘Table 2’!$B$5:$B$10:**Itâ€™s the lookup range. The formula looks for the value of**Cell B5**of the**INDEX**–**MATCH**sheet in the**range B5:B10**of the**Table 2Â**sheet.

- Finally, drag the Fill Handle down to merge two tables.

- Alternatively, we can also use this formula to merge tables from the same sheet.
- You can see
**Table 1**and**Table 2**on the same sheet.

- Now, select
**Cell D5**and type the formula below:

`=INDEX($G$5:$G$10,MATCH($B5,$F$5:$F$10,0))`

This formula works the same as the previous one we used in this method.

- After that, press
**Enter**and drag the**Fill HandleÂ**down.

- Finally, you will see results like the picture below.

**Read More: **How to Merge Two Tables in Excel Using VLOOKUP

## 2. Use Excel Power Query to Join Two Tables Based on One Column

Another way to merge two tables is to use the **Excel Power Query **feature. Here, we will use the previous dataset. But there will be no **Sales Amount **column in **Table 1**. The name of the first table is **Seller_Info**.

And **Table 2 **will contain the sales amount. The name of the second table is **Sales_Amount**. Here, the **Seller **column is the common column between the two tables.

Letâ€™s pay attention to the steps below to see how we can **merge two tables using Power Query in Excel.**

**STEPS:**

- Firstly, go to the sheet that contains the first table.
- Then, go to the
**Data**tab and select**From Table/Range**. It will open the**Power Query**window. It may take a few seconds.

- Secondly, in the
**Power Query**window, go to the**Home**tab and click on the**Close & Load**icon. A drop-down menu will appear. - Select
**Close & Load To**from there. It will open the**Import Data**dialog box.

- In the
**Import Data**box, select**Only Create Connection**and click**OK**to proceed.

- Repeat the above steps for the second table.
- After completing the steps, you will see the tables in the
**Queries & Connections**pan. Here,**Seller_Info**is the first table and**Sales_Amount**is the second table.

- In the following step, navigate to the
**Data**tab and select**Get Data**. A drop-down menu will appear. - Select
**Combine Queries >> Merge**from there. This will open the**MergeÂ**window.

- In the
**Merge**window, select the tables in the first two boxes. - Also, select
**Left Outer (all from first, matching from second)**in the**Join KindÂ**box. - Now, most importantly select the
**Seller**column in both tables. - Click
**OK**to proceed.

- As a result, you will see a merged table in the
**Power Query Editor**. But the**Sales Amount**column doesnâ€™t contain any value. - So, to insert values, click on the
**ExpandÂ**icon.

- A message box will appear.
- Unselect all columns from there and then, select
**Sales AmountÂ**again. - Also, uncheck â€˜
**Use original column name as prefix**â€™. - Click
**OK**to proceed.

- Instantly, you will see the merged table with values in the
**Sales AmountÂ**column.

- To load the merged table, go to the
**Home**tab of the**Power Query**window and click on the**Close & LoadÂ**icon. - Select
**Close & Load To**from the drop-down menu.

- In the following step, select
**Table**and**New worksheet**in the**Import DataÂ**box. - After that, click
**OK**to move forward.

- Finally, you will see the merged table in a new sheet named
**Merge 1**.

## 3. Merge Two Excel Tables Based on One Column with Copy & Paste Options

We can also use the **Copy**–**Paste **options to merge Excel Tables. But this process has a huge drawback. If the common column of the tables does not maintain the same order then, you canâ€™t apply this method. Here, you can see the order of the **Seller **column is the same in both tables.

Letâ€™s follow the steps below to see how we can apply this method.

**STEPS:**

- Firstly, select the
**range G5:G10**from the second table.

- Secondly, press
**Ctrl**+**C**to copy them. - After that, select
**Cell D5**.

- Finally, press
**Ctrl**+**V**to paste the values of the second table into the first table.

## Things to Remember

There are some things you need to remember while trying to merge two tables in Excel.

**Excel Power Query**can merge many tables. It is an add-in in Excel 2010 and 2013. But a built-in function in the newer versions of Excel.- The
**VLOOKUP**function is vulnerable sometimes and gives errors. You can use**the IFERROR function**to ignore the errors. - Moreover, if you are an
**Excel 365**user, then, you can use**the XLOOKUP function**. - You must apply the formulas carefully if the tables are stored in different sheets.

**Download Practice Book**

You can download the practice book from here.

## Conclusion

In this article, we have 3 easy methods to Merge Two Tables Based on One Column in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

**<< Go Back to Merge Tables in Excel | Merge in Excel | Learn Excel**