How to Use VLOOKUP to Merge Two Sheets in Excel

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.

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

Get FREE Advanced Excel Exercises with Solutions!
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