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 do the task effortlessly.

**Table of Contents**hide

## Download Practice Workbook

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

## 2 Steps to Merge Two Sheets Using VLOOKUP in Excel

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 **VLOOKUP ** function** to merge two Excel 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 do 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 of all, 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 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 **VLOOKUP to merge two Excel sheets**.

**Read More:** **How to Merge Excel Sheets into One Workbook (4 Suitable Ways)**

## Using XLOOKUP Function to Merge Two Excel Sheets

You can use **the XLOOKUP function** to **merge two 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 do 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 value 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 **XLOOKUP to merge two Excel sheets**.

**Read More:** **How to Merge Multiple Sheets in Excel (3 Easy Ways)**

## 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 the **List 1 for 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 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 of all, 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 **LOOKUP to merge two Excel sheets**.

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

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

## 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. Please visit our website **ExcelDemy** to explore more.