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

The two sample sheets contain data of student names and marks obtained for Physics and Math.

### Method 1 – Apply the VLOOKUP Function to Merge Tables from Different Sheets

Steps:

• Go toÂ E5 and insert the following formula
`=VLOOKUP(B5,'Dataset (Math)'!\$B\$5:\$D\$14,3,FALSE)`

Formula Explanation:

• B5Â is theÂ lookup_value.
• Excelwill look for the value inÂ B5Â (11001) in the rangeÂ B5:D14Â from theÂ Dataset (Math)
• 3states thatÂ ExcelÂ will findÂ 3rd columnÂ from the range.
• FALSEis forÂ Exact Match.
• PressÂ ENTERto get the output.

• Use theÂ Fill Handle toÂ AutoFillÂ up toÂ E14.

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

Steps:

• Go toÂ E5 and insert the following formula.
`=INDEX('Dataset (Math)'!\$D\$5:\$D\$14,MATCH('Dataset (Math)'!D5,'Dataset (Math)'!\$D\$5:\$D\$14,0))`

Formula Breakdown:

• MATCH(‘Dataset (Math)’!D5,’Dataset (Math)’!\$D\$5:\$D\$14,0)
• Output: 1
• INDEX(‘Dataset (Math)’!\$D\$5:\$D\$14,MATCH(‘Dataset (Math)’!D5,’Dataset (Math)’!\$D\$5:\$D\$14,0)) â†’ This becomes,
• INDEX(‘Dataset (Math)’!\$D\$5:\$D\$14,1
• Output: 76
• 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 Data.
• Select the Consolidate feature (see image).

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

• Add the marks for Math.
• Click OK.

• Excel will add the marks.

Read More: How to Merge Two Tables in Excel

### Method 4 –Â Use PowerQuery to Merge Tables from Different Sheets

Steps:

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

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

• Create a second table and name it Math.

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

• The Power Query Editor will appear.
• Insert the following formula:
`=Excel.CurrentWorkbook()`
• 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.

• 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 the Visual Basic Editor.

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

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

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF