How to Merge Excel Files Based on Column (3 Ways)

The following image shows the names and corresponding designations which we saved in a file named Merge Files.

This figure shows the names and salaries in the file named Merge Files (lookup).

How to Merge Excel Files Based on Column: 3 Ways

Method 1 – Using the VLOOKUP Function to Merge Files Based on Column

We will bring the Salary column from the Merge Files (lookup) file and put it in the file named Merge Files.

Steps:

• Make a column for salaries in Merge Files and use the following formula in cell D5 of that file.

The VLOOKUP Function looks for the value in cell B5, searches this value in range B5:C11 of the Merge Files (lookup) file (keep in mind that we have to use an Absolute Cell Reference), and returns the corresponding salary for the guy in cell B5. We set the column index number as 2 because the salaries are in the 2nd column. We want an exact match of the names so we chose FALSE.

• Press the Enter button, and you will see the salary of Jason Campbell, whose name is in cell B5.

• Use the Fill Handle to AutoFill down.

Method 2 – Merging Excel Files Based on the Column with INDEX and MATCH Functions

We will bring the Salary column from the Merge Files (lookup) file and put it in the file named Merge Files.

Steps:

• Make a column for salaries in Merge Files and use the following formula in cell D5 of that file:

Here, the MATCH Function looks up the value in cell B5 and returns the row number from the Merge Files (lookup) file for the corresponding value of B5. Then the INDEX Function returns the relatable Salary from the range C5:C11 in the Merge Files (lookup) file. Keep in mind that you should use the Absolute Cell Reference, otherwise, you face unexpected errors.

• Press the Enter button and you will see the salary of Jason Campbell, whose name is in cell B5.

• Drag the Fill Handle down to AutoFill.

Method 3 – Using the Power Query Editor to Merge Excel Files Based on Column

Steps:

• Open a new worksheet and select Data, then go to Get Data, choose From File, and select From Excel Workbook

• The Import Data window will appear. Select the workbook Merge Files (from our sample) and click Open.

• The Navigator window will show up. Select power query as we saved the names and designations in this sheet of the file named Merge Files.
• Click on the arrow next to Load and select Load To.

• You will see a dialog box. Choose Only Create Connection and click OK.

• This will add the power query sheet from the Merge Files workbook in the Queries & Connections section.

• Select Data, choose Get Data, go to From File, and pick From Excel Workbook

• The Import Data window will appear. Select Merge Files (lookup) and Open

• The Navigator window will show up. Select salary as we saved the names and salaries in this sheet of the file named Merge Files (lookup).
• Select Load To.

• Choose Only Create Connection and click OK.

• This will add the salary sheet from the Merge Files (lookup) file in the Queries & Connections section.

• Go to Data, then to Get Data.
• Choose Combine Queries and select Merge.

• The Merge window will appear. Select power query from the first drop-down icon and salary from the second drop-down icon.
• Click on the Name columns of both queries.
• Click OK.

• The following table will appear in the Power Query Editor.

• Click on the marked icon in the salary column and select Salary.
• Click OK.

• You will see Name, Designation, and Salary together in Power Query Editor.
• Select Close & Load.

• This operation will show the information in a new Excel Table in a new sheet.

Practice Section

We attached a practice section in the download file so you can experiment with these methods.

Download the Practice Workbook

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