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

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.

Method 1 – Using the VLOOKUP Function to Merge Two Excel Sheets Based on One Column

Steps:

• Copy the Departmental Sheet and name it VLOOKUP.
• Create a new column named Salary in column D.

• Go to Cell D5.
• Put the following formula:

• Press the Enter button.

• Drag the Fill Handle icon down to get the result of the full list.

Method 2 – Combining MATCH and INDEX Functions to Merge Two Sheets in Excel

Steps:

• Create a new joined table similar to Method 1.
• Go to Cell D5 and put the formula based on the MATCH and INDEX functions.

• Press Enter and pull the Fill Handle icon down to get the full result.

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

Steps:

• Open a new Excel file.

• Click on the Data tab first.
• Choose the Get Data option.
• Select File and Excel Workbook.

• We chose our Excel file from the File Manager.
• Click on the Import button.

• The Navigator window appears.
• Choose one of the sheets to merge.
• 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.
• Press OK.

• Look at the Queries & Connections section.

• We can see that the selected sheet has been added.
• Add the Salary Sheet in the power query.

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

• Choose Merge Queries from the main tab.

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

• Select the column from both sheets.
• Press the OK button.

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

• Mark the Column2 option.
• Press the OK button.

• All values are showing.

• Click on the Reduce Rows tab.
• Choose 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.
• Press the OK button.

• All unnecessary rows have been removed.

• Choose the Use First Row as Headers option.

• Here’s the result.

Download the Practice Workbook

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