How to Merge Two Excel Sheets Based on One Column (3 Ways)

In small businesses or corporate organizations, we use MS Excel to collect data. Information about the same people or entity is stored in different sheets. Sometimes we need to get information those are stored in different sheets. In this article, we will discuss how to merge two Excel sheets based on one column.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Merge Two Excel Sheets Based on One Column

We have the salary and departmental information of some people on two different sheets. Here, we will show 3 ways to merge two sheets based on one column.

Sample dataset to show how to merge two Excel sheets based on one column


1. Merge Two Sheets Using VLOOKUP Function

In this section, we will use the VLOOKUP function to merge two sheets in Excel. Merging two sheets means merging the information of two sheets. We can do this in the existing sheet. If we do not want to make any changes to the existing datasheet, we can apply this to a new sheet.

📌 Steps:

  • First, copy the Departmental Sheet and named it VLOOKUP.
  • Here, the Name and Department columns exist. Create a new column named Salary.

=VLOOKUP(B5,'Salary Sheet'!$B$5:$C$9,2,FALSE)

Apply VLOOKUP formula to merge two Excel sheets based on one column

  • Now, press the Enter button.

  • Then, drag the Fill Handle icon to get the result of the full list.

Look at the sheet. We get Salary of the all employees after merging.

The applied formula is showing the result by merging information from the Departmental Sheet and the Salary Sheet based on the Name column.

Read More: How to Merge Multiple Sheets in Excel (3 Easy Ways)


2. Apply Combination of MATCH & INDEX Functions to Merge Two Sheets

Here, we will use the combination of the MATCH and INDEX functions to merge two sheets based on one column in Excel. First, we get the position of the row in the range. Then get the relative data of that cell.

📌 Steps:

  • Go to Cell D5 and put the formula based on the MATCH & INDEX functions.
=INDEX(‘Salary Sheet’!$C$5:$C$9,MATCH($B5,’Salary Sheet’!$B$5:$B$9,0))

Use a formula based on INDEX & MATCH functions to merge two sheets based on one column

  • Now, press Enter and pull the Fill Handle icon downwards to get the result.

Formula Explanation:

  • MATCH($B5,’Salary Sheet’!$B$5:$B$9,0)

The MATCH function shows the position of Cell B5 in the Range B5:B9.

Result: 1

  • INDEX(‘Salary Sheet’!$C$5:$C$9,MATCH($B5,’Salary Sheet’!$B$5:$B$9,0))

The INDEX function returns the relative data of the showing position from the Range C5:C9 of the Salary Sheet.

Result: 1000

Read More: How to Merge All Sheets into One in Excel (6 Quick Ways)


3. Merge Two Sheets Based on One Column Using Excel Power Query

Here, we will apply a power query in a new excel file to merge two sheets based on one column.

📌 Steps:

  • Enter a new Excel file.

  • Click on the Data tab first.
  • Choose the Get Data option.
  • Then, proceed to select an Excel file.

Add Excel file for power query

  • We choose our desired Excel file from the File Manager.
  • Then, click on the Import button.

  • The Navigator window appears.
  • Choose one of the sheets to merge.
  • Then, look at the bottom section.
  • Press the down arrow of the Load option.
  • Choose the Load to option from there.

Merge Two Sheets Based on One Column Using Excel Power Query

  • The Import Data window appears.
  • Tick the Only Create Connection option.
  • Finally, press OK.

  • Look at the Queries & Connections section.

We can see that the selected sheet has been added.

  • Similarly, we add Salary Sheet in the power query.

  • Double-click on the Departmental Sheet and enter the power query window.

  • Now, choose Merge Queries from the main tab.

  • The Merge window appears.
  • We choose our second sheet from the list.

  • Then, we select the column from both sheets.
  • After that, press the OK button.

Select a column to merge two sheets in Excel

  • We can see that three rows are showing.
  • Click on the right upper section of the Salary Sheet column.

  • Now, mark the Column2 option.
  • Then, press the OK button.

  • Here, all values are showing.

There are some blank rows in the dataset.

  • Click on the Reduce Rows tab.
  • Then, choose to Remove Blank Rows from the Remove Rows.

Merge Two Sheets Based on One Column Using Excel Power Query

  • We can see all blank rows are removed. Still, there is an unnecessary row that exists.

  • Click on the Remove Top Rows option from the Remove Rows tab.

Remove top rows from Excel power query

  • A dialog box appears to put the number of rows. We input 1 on the box.
  • Then, press the OK button.

Input the number of rows

  • All unnecessary rows have been removed.

  • Now, make the first row a header.
  • Choose the Use First Row as Headers option.

Make the first row as header

  • Finally, look at the power query.

Read More: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)


Conclusion

In this article, we described how to merge two sheets based on one column in excel with detailed explanations. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo