How to Merge Tables from Different Sheets in Excel (5 Easy Ways)

We will use the two sample sheets with data of student names and marks obtained for Physics and Math.

Method 1: Apply VLOOKUP Function to Merge Tables from Different Sheets

Steps:

• Go to E5and insert the following formula

• Use the Fill Handleto AutoFill up to E14.

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

Method 2: Use a Combination of INDEX and MATCH Functions to Merge Tables

Steps:

• Go to E5and insert the following formula.

• Press ENTER to get the output.

• AutoFill up to E14.

Method 3: Merge Tables from Different Sheets Using Consolidate Feature

Steps:

• Select D5:D14.
• Go to the Data
• Select Consolidate feature(see image).

• Choose the function Sum.
• Select the reference for the marks of Physics.
• Click Add.

• Add the marks for Math.
• Click OK.

• Excel will add the marks.

Read More: How to Merge Two Tables in Excel

Method 1: Use PowerQuery to Merge Tables from Different Sheets

Steps:

• Convert data ranges to tables.
• Select B4:D14.
• Press CTRL+T.
• Create Tablebox will appear. Check the My table has headers.
• Click OK.

• Excel will create a table.
• Rename the table as Physics.

• Create a table and name it Math.

• Go to the Data
• Select Get Data.
• Choose From Other Sources.
• Select Blank Query.

• Power Query Editor will appear.
• Insert the following formula,

• Press ENTER.
• Select the icon shown in the image.

• Uncheck the ‘Use original column name as prefix’ box.
• Click OK.

• Excel will merge the tables.
• Click Close & Load.

• Excel will load the combination in a new worksheet.

• A new row Query1!ExternalData_1 appears. To remove this, select the drop-down.
• Uncheck the box for Query1!ExternalData_1.
• Click OK.

• The tables merged. The final output will be as shown in the image.

Method 5: Use VBA Code to Merge Tables from Different Sheets

Steps:

• Press ALT+F11 to open Visual Basic Editor.

• Go to the Insert
• Select Module.

• A new module will appear. Insert the following code.

Sub MergeTable()
Worksheets("VBA").Range("B4:D14").Value = Worksheets("Dataset (Physics)").Range("B4:D14").Value
Worksheets("VBA").Range("E4:E14").Value = Worksheets("Dataset (Math)").Range("D4:D14").Value
End Sub

• Press F5 to run the code. Excel will merge the columns from those tables.

• Format as required.

Things to Remember

• Use absolute reference to lock a cell.
• The VLOOKUP function can only look to the right to get data.

Download Practice Workbook

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