# How to Merge Two Tables Based on One Column in Excel (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents

## 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:

`=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.

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF