If you are looking for VLOOKUP to merge two Excel sheets, you have come to the right place. In many cases, we have data in two different Excel sheets, andÂ we need to merge them into one new sheet. Here, we will walk you through some easy steps to takeÂ the task effortlessly.

**Table of Contents**Expand

## How to Use VLOOKUP to Merge Two Sheets in Excel: with Easy Steps

The following Students List 1 dataset has the Student ID and Name columns. It is present in the Student List 1 Excel sheet.Â You can notice that the Student ID is in irregular order.

Along with that, the dataset Students List 2 has the Student ID and Department columns, and it is present in the Student List 2 Excel sheet. Here, You can notice that the Student ID is in irregular order.

In addition, the following dataset has the Student ID, Name, and Department column. You can easily notice that the Student ID is now in regular ascending order.

Next, we will use **the VLOOKUP function**Â to merge two sheets into the following new Excel sheet.

Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.

Letâ€™s go through the following steps to take the task.

### Step-1: Inserting Data from First Sheet

In this step, we will insert the Name from the Student List 1 sheet into the following new sheet. To do so, we will use** the VLOOKUP function**.

- First, we will type the following formula in cell
**C5**.

`=VLOOKUP(B5,'Student List 1'!$B$5:$C$11,2,FALSE)`

**Formula Breakdown**

**VLOOKUP(B5,â€™Student List 1â€²!$B$5:$C$11,2,FALSE) â†’The VLOOKUP function**searches for values in a defined table array.**B5 â†’**is the**lookup_value**.**â€˜Student List 1â€™!$B$5:$C$11â†’**is the**table_array,**which is present in the sheet**Student List 1**.**2**â†’ is**col_index_num****FALSEâ†’**indicates the**Exact match**.**VLOOKUP(B5,â€™Student List 1â€²!$B$5:$C$11,2,FALSE) â†’**becomes**Output:****Mike****Explanation:**here,**Mike**is the**Name**for**Student ID S-101**.

- After that, press
**ENTER**.

Then, you can see the result in cell **C5**.

- Furthermore, we will
**drag down**the formula with the**Fill Handle**tool.

Therefore, You can see the complete** Name **column in the new Excel sheet.

### Step-2: Employing Data from the Second Sheet

In this step, we will insert the** Department** from the** Student List 2** sheet into the following **new sheet**.

- First, we will type the following formula in cell
**D5**.

`=VLOOKUP(B5,'Student List 2'!$B$5:$C$11,2,FALSE)`

**Formula Breakdown**

**VLOOKUP(B5,â€™Student List 2â€²!$B$5:$C$11,2,FALSE) â†’The VLOOKUP function**searches for values in a defined table array.**B5 â†’**is the**lookup_value**.**â€˜Student List 2â€™!$B$5:$C$11â†’**is the**table_array,**which is present in the sheet**Student List 2**.**2**â†’ is**col_index_num****FALSEâ†’**indicates the**Exact match**.**VLOOKUP(B5,â€™Student List 2â€²!$B$5:$C$11,2,FALSE) â†’**becomes**Output:****Math****Explanation:**here,**Math**is the**Department**for**Student ID S-101**.

- At this point, press
**ENTER**.

Therefore, you can see the result in cell **D5**.

- Moreover, we will
**drag down**the formula with the**Fill Handle**tool.

Hence, You can see the complete **Department** column.

Therefore, we use theÂ **VLOOKUP **function to merge two Excel sheets.

## How to Use XLOOKUP Function to Merge Two Excel Sheets

You can use **the XLOOKUP function** to merge or combine data from multiple Excel sheets. However, **the XLOOKUP function** is available in Excel 2021 and the latest versions of Excel.

Letâ€™s go through the following steps to take the task.

**Steps:**

- In the beginning, we will type the following formula in cell
**C5**.

`=XLOOKUP(B5,'Student List 1'!$B$5:$B$11,'Student List 1'!$C$5:$C$11)`

** **

**Formula Breakdown**

**XLOOKUP(B5,â€™Student List 1â€²!$B$5:$B$11,â€™Student List 1â€²!$C$5:$C$11)Â â†’ the XLOOKUP function**searches for data or value in a table array and returns the result value in another location.**B5Â â†’**is the**lookup_value**.**â€˜Student List 1â€™!$B$5:$B$11 â†’**is the**lookup_array**.**â€˜Student List 1â€™!$C$5:$C$11 â†’**is the**return_array**.**XLOOKUP(B5,â€™Student List 1â€²!$B$5:$B$11,â€™Student List 1â€²!$C$5:$C$11)Â â†’**becomes**Output:****Mike****Explanation:**here,**Mike**is the**Name**for**Student ID S-101**

- Moreover, press
**ENTER**.

Then, you can see the result in cell **C5**.

- After that, we will
**drag down**the formula with the**Fill Handle**tool.

Therefore, you can see the complete** Name **column.

- Furthermore, to find out the
**Department**for the corresponding**Student ID**, we will type the following formula in cell**D5**.

`=XLOOKUP(B5,'Student List 2'!$B$5:$B$11,'Student List 2'!$C$5:$C$11)`

Here, **the XLOOKUP function **searches for data or values in a table array and returns the result value in another location.

- At this moment, press
**ENTER**.

As a result, you can see the result in cell **D5**.

- Afterward, we will
**drag down**the formula with the**Fill Handle**tool.

Then, you can see the complete **Department** column.

Therefore, we use the **XLOOKUP **function to merge two Excel sheets.

## How to Use of LOOKUP Function to Merge Two Excel Sheets

Excel **LOOKUP **function is available in all versions of Excel. Therefore, if you do not have higher versions of Excel, you can use **the LOOKUP function** to merge two Excel sheets.

Here, one thing that must be remembered while using** the LOOKUP function** is that the lookup_vector must be in the correct order in the sheets.

Here, we created the Students List 1 dataset that has the Student ID and Name columns. It is present in List 1 for the **LOOKUP** Function Excel sheet.Â Along with that, you can notice that the Student ID is in regular ascending order, which is a must while using the **LOOKUP** function.

Furthermore, we created the Students List 2 dataset that has the Student ID and Department columns. It is present in List 2 for the **LOOKUP** Function Excel sheet.Â Here, You can notice that the Student ID is in regular ascending order.

Next, we will merge these two Excel sheets into one new sheet. Here, the Student ID in the new sheet is in the same order as it is in Student List 1, and Student List 2.

**Steps:**

- First, we will type the following formula in cell
**C5**.

`=LOOKUP(B5,'List 1Â for LOOKUP Function'!$B$5:$B$11,'List 1Â for LOOKUP Function'!$C$5:$C$11)`

**Formula Breakdown**

**LOOKUP(B5,â€™List 1Â for LOOKUP Functionâ€™!$B$5:$B$11,â€™List 1Â for LOOKUP Functionâ€™!$C$5:$C$11) â†’ the LOOKUP function**searches for a value in a**single row**or**single column**and returns the value to another location.**B5**â†’ is the**lookup_value**.**â€˜List 1Â for LOOKUP Functionâ€™!$B$5:$B$11**â†’ is the**lookup_vector**.**â€˜List 1Â for LOOKUP Functionâ€™!$C$5:$C$11**â†’ is the**resul_vextor**.**LOOKUP(B5,â€™List 1Â for LOOKUP Functionâ€™!$B$5:$B$11,â€™List 1Â for LOOKUP Functionâ€™!$C$5:$C$11) â†’**becomes**Output:****Mike****Explanation:**Here,**Mike**is the**Name**for**Student ID S-101**.

- After that, press
**ENTER**.

Then, you can see the result in cell **C5**.

- Furthermore, we will
**drag down**the formula with the**Fill Handle**tool.

Therefore, You can see the complete** Name **column in the new Excel sheet.

- After that, to find out the
**Department**for the corresponding**Student ID**, we will type the following formula in cell**D5**.

`=LOOKUP(B5,'List 2 for LOOKUP Function'!$B$5:$B$11,'List 2 for LOOKUP Function'!$C$5:$C$11)`

Here, **the LOOKUP function **searches for a value in a single row or single column and returns the value to another location.

** **

- Afterward, press
**ENTER**.

As a result, you can see the result in cell **C5**.

In addition, we will** drag down** the formula with the **Fill Handle **tool.

Therefore, you can see the complete **Department** column.

Hence, we use the **LOOKUP** function to merge two Excel sheets.

## Things to Remember

- While using
**the VLOOKUP function**, you must keep the**Student ID**column as the**first column**in the**lookup_array** - Make sure to set
**FALSE**in**the VLOOKUP function**, this will return an**Exact match**.

**Practice Section**

You can download the above Excel file to practice the explained methods.

**Download Practice Workbook**

You can download the **Excel file** and practice while you are reading this article.

## Conclusion

Here, we tried to show you some easy steps to VLOOKUP to merge two Excel sheets. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below.

**<< Go Back ToÂ Merge Sheets in Excel | Merge in Excel | Learn Excel**