Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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


Download Practice Book

You can download the practice book from here.


3 Easy Ways to Merge Two Tables Based on One Column in Excel

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.

excel merge two tables based on one column

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

Merge Two Tables Based on One Column Using Formula in Excel

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.

Merge Two Tables Based on One Column Using Formula in Excel

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 INDEXMATCH functions to merge two tables in Excel. The general form of the INDEXMATCH 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.

Merge Two Tables Based on One Column Using Formula in Excel

In this formula, we have merged two tables from the sheets named Table 2 and INDEXMATCH.

  • 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 INDEXMATCH 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 INDEXMATCH sheet in the range B5:B10 of the Table 2 sheet.

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

Merge Two Tables Based on One Column Using Formula in Excel

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

  • After that, press Enter and drag the Fill Handle down.

Merge Two Tables Based on One Column Using Formula in Excel

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

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

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Instantly, you will see the merged table with values in the Sales Amount column.

Use Excel Power Query to Join Two Tables Based on One 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.

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

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

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

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.

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

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

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


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.

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo