Using VLOOKUP to Merge Two Sheets in Excel (with Easy Steps)

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.

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.

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

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 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 VLOOKUP to merge two Excel sheets.

merging 2 Excel sheets into One New Sheet using VLOOKUP function

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.

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

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

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


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo