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.

## How to Merge Tables from Different Sheets in Excel: 5 Easy Methods

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 in Excel

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

** **

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

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

Download this workbook and practice while going through the article.

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

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