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

Last updated: January 24, 2023
Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In Excel, we often work with large datasets. While working with these datasets, we frequently need to merge tables from multiple sheets to analyze them properly. In this article, I will explain 5 ways in Excel to merge tables from different sheets.


Download Practice Workbook

Download this workbook and practice while going through the article.


5 Easy Methods to Merge Tables from Different Sheets in Excel

This is the worksheet I am going to use to explain the methods on how to merge tables from different sheets.in Excel. We have several students along with their Student ID and their Marks. 1st sheet contains marks for Physics and the 2nd one is for Math. I am going to merge the tables for different subjects to describe the methods.

dataset for merge tables from different sheets


1. Apply VLOOKUP Function to Merge Tables from Different Sheets

Now, I will show how to use the VLOOKUP function to merge tables from different sheets. I will add the marks for the math column using this method. Let’s do it step by step.

Steps:

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

VLOOKUP function merge tables from different sheets

Formula Explanation:

  • B5 is the lookup_value.
  • Excel will look for the value in B5 (11001) in the range B5:D14 from the Dataset (Math)
  • Then, 3 states that Excel will find 3rd column from the range.
  • Finally, FALSE is for Exact Match.
  • Now, press ENTER to get the output.

  • Finally, use the Fill Handle to AutoFill up to E14.

VLOOKUP function merge tables from different sheets

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


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

In this section, I will show how to merge tables from different sheets using a combination of INDEX and MATCH functions. I will add the column of marks for math using this method.

Steps:

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

INDEX-MATCH functions merge tables from different sheets

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
  • Then, press ENTER to get the output.

  • AutoFill up to E14.

INDEX-MATCH functions merge tables from different sheets


3. Merge Tables from Different Sheets Using Consolidate Feature

You can also use the Consolidate Feature to merge tables. The difference here is that you cannot directly merge the columns. However, you can apply some operations in this feature. For example, I am going to get the summation of the marks for physics and math using this method.

Steps:

  • Select D5:D14.
  • Then, go to the Data
  • After that, select Consolidate feature (see image).

  • Choose the function Sum.
  • Select the reference for the marks of Physics.
  • Then, click Add.

  • Similarly, add the marks for Math.
  • Then, click OK.

  • Excel will add the marks.

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


4. Use PowerQuery to Merge Tables from Different Sheets

The first method that I am going to discuss is the use of the PowerQuery feature to merge tables. Let’s do it step by step.

Steps:

  • First of all, you must convert data ranges to tables. To do so,
  • Select B4:D14.
  • Then, press CTRL+T.
  • Create Table box will appear. Check the My table has headers
  • Then, click OK.

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

  • Similarly, create a table and name it Math.

PowerQuery to merge tables from different sheets

  • After that, go to the Data
  • Then, select Get Data.
  • After that, choose From Other Sources.
  • Finally, select Blank Query.

  • Power Query Editor will appear.
  • Write down the following formula,
=Excel.CurrentWorkbook()
  • Press ENTER.
  • Then, select the icon shown in the image.

PowerQuery to merge tables from different sheets

  • Next, uncheck the ‘Use original column name as prefix’ box.
  • Then, click OK.

  • Excel will merge the tables.
  • Then, click Close & Load.

PowerQuery to merge tables from different sheets

  • Excel will load the combination in a new worksheet.

  • There is a new row Query1!ExternalData_1. To get rid of this, select the drop-down.
  • Then, uncheck the box for Query1!ExternalData_1.
  • After that, click OK.

PowerQuery to merge tables from different sheets

  • You will get the tables merged. The final output will be like this

Read More: How to Combine Two Tables Using Power Query 


5. Use VBA Code to Merge Tables from Different Sheets

Now, I will use a VBA code to merge tables. I will combine the marks of physics and math in a new sheet.

Steps:

  • Press ALT+F11 to open Visual Basic Editor.

  • Go to the Insert
  • Select Module.

Use of VBA to merge tables from different sheets

  • A new module will appear. Write down 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.

Use of VBA to merge tables from different sheets

  • Format as you wish.

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


Things to Remember

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

Conclusion

In this article, I have explained 5 easy ways in Excel to merge tables from different sheets. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.


Related Articles

Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo