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.

Sample dataset to show how to merge two Excel 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.

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

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

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.

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.

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

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF