# How to Use VLOOKUP to Merge Two Sheets in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF