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
Get FREE Advanced Excel Exercises with Solutions!
