The following dataset has the Student ID and Name columns. The Student IDs are in irregular order.

The second dataset has the Student ID and Department columns. The Student ID is also in irregular order.

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

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

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

- Press
**ENTER**.

You can see the result in cell **C5**.

**Drag down**the formula with the**Fill Handle**tool.

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

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

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

- Press
**ENTER**.

You can see the result in cell **D5**.

**Drag down**the formula with the**Fill Handle**tool.

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

- Use theÂ
**VLOOKUP**function to merge two Excel sheets.

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

**Steps:**

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

- Press
**ENTER**.

You can see the result in cell **C5**.

**Drag down**the formula with the**Fill Handle**tool.

You can see the complete** Name **column.

- To find the
**Department**for the corresponding**Student ID**, - Enter 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 in another location.

- Press
**ENTER**.

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

**Drag down**the formula with the**Fill Handle**tool.

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

- Use the
**XLOOKUP**function to merge two Excel sheets.

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

We created the Students List 1 dataset with Student ID and Name columns. It is present in List 1 for the **LOOKUP** Function Excel sheet.Â The Student ID is in regular ascending order, which is necessary when using the **LOOKUP** function.

We created the Students List 2 dataset with the Student ID and Department columns. It is present in List 2 for the LOOKUP Function Excel sheet. The Student ID is in regular ascending order.

We will merge these two Excel sheets into one new sheet. The Student IDs on the new sheet are in the same order as they are in Student List 1 and Student List 2.

**Steps:**

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

- Press
**ENTER**.

You can see the result in cell **C5**.

**Drag down**the formula with the**Fill Handle**tool.

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

- To find the
**Department**for the corresponding**Student ID**, - enter 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 column and returns it to another location.

** **

- Press
**ENTER**.

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

**Drag down**the formula with the**Fill Handle**tool.

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

- 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 the Practice Workbook**

You can download the **Excel file** and practice.

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