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.

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.

1. Using the VLOOKUP Function Merge Two Excel Sheets Based on One Column

In this section, we will use the VLOOKUP function to merge two Excel sheets. 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.

• Go to Cell D5.
• Put the following formula based on the VLOOKUP function.

• 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 shows the result by merging information from the Departmental Sheet and the Salary Sheet based on the Name column.

2. Combining MATCH & INDEX Functions to Merge Two Sheets in Excel

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 and INDEX functions.

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

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.

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

• 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 the 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 chose our second sheet from the list.

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

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

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

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

• All unnecessary rows have been removed.

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

• Finally, look at the power query.

Download Practice Workbook

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

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.

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