How to Merge Two Tables in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In the workspace, we frequently encounter the need to merge two separate tables with each other. Merging two separate tables will increase the clarity of the information and make interpretation easier. If you are curious to know how you can merge two tables in Excel, then this article may come in handy for you. In this article, we discuss how you can merge two separate tables in Excel with an elaborate explanation.

We will use the following dataset in order to create a relationship between the two tables in Excel with duplicate values. Both of the data sets have a common column. The common column is the Product Id. The merging of the two tables will be based on this column.

How to Merge Two Tables in Excel


1. Using VLOOKUP Function to Merge Two Tables in Excel

The VLOOKUP function will help us to look for the value from one column to another column. Then copy that value, and paste it to the destination cell. Which eventually helped us to merge two tables in Excel.

Steps

  • In the below image, we have two tables that we want to merge together
  • In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.

Using VLOOKUP Function to Merge Two Tables in Excel

  • To do this, first, select the cell I4 and enter the following formula:

=VLOOKUP(F4,$B$4:$D$10,2,FALSE)

vlookup formula input

  • And then drag the Fill Handle to cell I10.
  • Doing this will fill the range of cell I4:I10 with the first column of the first table, matching with the Product Id column.

one column merge between two tables

  • To add the second column, select cell J4 and enter the following formula:

=VLOOKUP(F4,$B$4:$D$10,3,FALSE)

vlookup formula input for second column .

  • And then drag the Fill Handle to cell J10.
  • Doing this will fill the range of cells J5:J10 with the first column of the first table, matching with the Product Id column.

  • Now we need to format new columns to match the rest of the cells.
  • Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
  • A small paint brush appears in the place of the cursor.
  • With that cursor, select the range of cells I4:J10.

formatted the output

  • Finally, you can see the two tables are now merged and formatted.

two separate table merge using functions.

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


2. Applying the XLOOKUP Function to Merge Two Tables

The XLOOKUP function will work in the almost similar way the previous method works. In XLOOKUP, the user needs to enter the output range argument that will be returned instead of the serial no of the column in a table.

Steps

  • In the below image, we got two tables that we want to merge together
  • In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.

Utilizing XLOOKUP Function to Merge Two Tables in Excel

  • To do this, first, select the cell I4 and enter the following formula:

=XLOOKUP(F4,$B$4:$B$10,$C$4:$C$10)

  • Then drag the Fill Handle to cell I10.
  • Doing this will fill the range of cells I4:I10 with the first column of the first table, matching with the Product Id column.

first column merged

  • To add the second column, select cell J4 and enter the following formula:

=XLOOKUP(F4,$B$4:$B$10,$D$4:$D$10)

XLOOKUP formula input to merge two tables

  • Then drag the Fill Handle to cell J10.
  • Doing this will fill the range of cells J4:J10 with the first column of the first table, matching with the Product Id column.

second column is merged

  • Now we need to format new columns to match the rest of the cells.
  • Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
  • A small paintbrush appears in the place of the cursor.
  • With that cursor, select the range of cells I4:J10.

output formatting

  • Finally, you can see the two tables are now merged and formatted.

merge two enage of cells by matching values


3. Merging Two Tables with Excel Power Query 

Power Query is one of the most powerful tools in Excel. Power query has many powerful default features that can solve many issues instantaneously

Steps

  • In the below image, we have two tables that we want to merge together
  • In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.

Applying Power Query to merge two tables in excel

  • To add these two data tables, go to Data > Get Data.
  • Furthermore, proceed to From Other Sources > From Table/Range.

Input table to power query

  • A small dialog box will appear.
  • In that dialog box, you need to enter the range of the table and tick the My table has headers box.
  • Click OK after this.

  • For the second table, do the same thing and add the table to the Power Query.
  • In the Power Query create a table dialog box, specify the range of the table, and tick the check box My table has headers.
  • Click OK after this.

  • After uploading both of the tables to the power query, we can merge them together using the combine feature.
  • For this, open the power query editor (clicking OK in the previous step will automatically launch the editor).
  • In the power query editor, go to the Home tab.
  • And from the Home tab, go to the Combine group. And then click on the Merge Queries.
  • From the drop-down menu, click on the Merge Queries.

merge queries using combine

  • In the new window named Merge, choose Table 1 as the first table
  • In the second dropdown menu, choose Table 2 as the second table.
  • In the below drop-down menu, choose Left Outer (all from the first, matching from the second).
  • This dropdown option means that the matching will start with the first table from the left side, then the right side matching part will come eventually.
  • Click OK after this.

selection of the merged table

  • Right after pressing OK, you will see that one column named Table2 is appended with the first table.
  • But this column is actually a shrunken version of Table2.
  • All the columns are hidden in this Table2 column.
  • To show all of the columns of Table2, click on the right top corner of the Table2 column header.

expanding merged table

  • Then in the expanded menu, only tick the Quantity and Cost check box, as we already have the Product Id in the first table.
  • Uncheck the Use Original Column name as a prefix box.
  • Click OK after this.

  • After clicking OK, you will notice that the two columns are now added to the first table.

combine command to merge tables

  • Then click on Close and Load from the Home tab.
  • Then from the drop-down menu, click on Close and Load To.

load the two merge table to the worksheet.

  • And select Table in the Select how you want to view this data in your workbook
  • Then choose the Existing worksheet and then select cell B13.
  • Click OK after this.

  • After this, the table will be loaded into cell B13:F19.
  • And we can see that both of the tables are now merged.

merging queries to merge two tables


4. Combining INDEX and MATCH Functions

The INDEX and MATCH functions will help us merge the two tables in Excel by matching the value and then indexing the value from the second table to the first table.

Steps

  • In the below image, we got two tables that we want to merge together
  • In order to merge a table, we must have a common column in between them. For the given tables, the common column is the Product Id column.

Combining INDEX and MATCH Functions to Merge Two Tables in Excel

  • To do this, first, select the cell I4 and enter the following formula:

=INDEX($C$4:$C$10,MATCH($F4,$B$4:$B$10,0))

  • And then drag the Fill Handle to cell I10.
  • Doing this will fill the range of cells I5:I10 with the first column of the first table, matching with the Product Id column.

first column of the first table are merged to second column

  • To add the second column, select cell J4 and enter the following formula:

=INDEX($D$4:$D$10,MATCH($F4,$B$4:$B$10,0))

  • And then drag the Fill Handle to cell J10.
  • Doing this will fill the range of cells I4:I10 with the first column of the first table, matching with the Product Id column.
  • This will complete the merging operation.

merging the second column of the first table

  • Now we need to format new columns to match the rest of the cells.
  • Select a range of cells D4:D10 and then click on the format painter icon from the Clipboard group in the Home tab.
  • A small paintbrush appears in the place of the cursor.
  • With that cursor, select the range of cells I4:J10.

formatting the output

  • Finally, you can see the two tables are now merged and formatted.

using functions to merge tables

🔎 Formula Breakdown

  • MATCH($F4,$B$4:$B$10,0)

This function will look for the exact value specified in the first argument in the array/ range of cells mentioned in the second argument. In this case, it will look for the value in the cell F4 in the lookup array in the B4:B10, and return the serial of that value in that range.

  • INDEX($C$4:$C$10,MATCH($F4,$B$4:$B$10,0))

After we got the serial of the matched value in the lookup array, then using that serial, it will look for the value of the same serial in the other column (first argument) in the table.


5. Using Excel Copy-Paste Feature to Merge Two Tables

Compared to the previous methods, this one is quite straightforward. We will directly copy and paste the columns of the second table to the first table.

Steps

  • Like previous methods, we need to have two tables with a common column.
  • At the same time, the common column values in both of the tables have to be in the same serial.

Use of Excel Copy-Paste Feature to Merge Two Tables in Excel

  • First, select the second and the third column of the first column and then right-click on the mouse.
  • From the context menu, click on the Copy.

  • Then select cell I4 and right-click on the mouse again.
  • From the context menu, click on Paste.

pasting copied cells

  • Doing this will paste the table columns into the second table.
  • Pasting the first table columns into the second table column will finally merge the two tables.

copy and paste to merge two tables

Read More: How to Merge Two Tables Based on One Column in Excel


Things to Remember

  • You need to maintain the same serial for the column entries in the common columns in both of the tables.
  • In the Power Query method, do not select the second table in the first place. Always select the first table from which the matching will start in the first place.

Download Practice Workbook

Download this practice workbook below.


Conclusion

To sum it up, the issue of how we can merge two separate tables in Excel with duplicate values is answered here in 5 different ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciated.


<< 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.
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo