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

**Table of Contents**hide

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

### 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)`

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

**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))`

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

### 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**.

** **

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

** **

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

** **

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

** **

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

** **

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

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

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