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 VLOOKUP Function Merge Two Excel Sheets Based on One Column
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.
- 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 is showing 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.
- Go to Cell D5 and put the formula based on the MATCH & INDEX functions.
- Now, press Enter and pull the Fill Handle icon downwards to get the result.
- MATCH($B5,’Salary Sheet’!$B$5:$B$9,0)
The MATCH function shows the position of Cell B5 in the Range B5:B9.
- 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.
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.
- 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 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.
- 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.
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.