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.

How to use VLOOKUP to Merge Two Excel Sheets

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)

Use of VLOOKUP Function to Merge Two Excel Sheets

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.

dragging VLOOKUP formula with Fill Handle tool to merge two Excel sheets

Hence, You can see the complete Department column.

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

merging 2 Excel sheets into One New Sheet using VLOOKUP function


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.

applying XLOOKUP function to merge two sheets in Excel

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.

Making IIst for LOOKUP function to merge 2 Excel sheets

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.

Applying the Fill Handel Tool to Merge 2 Excel Sheets into One Sheet

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.

Merging Two sheets in Excel using LOOKUP Function

  • 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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo