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

The article will provide you some basic tips on how to merge Excel files based on column. Sometimes, we may have different information about the same people or items in different Excel workbooks. Therefore, we may require to merge that information in one Excel sheet. In this article, we have data on some peoples’ names and their designation in one Excel Workbook and their names and salaries in another workbook. We are going to show their names, designations and salaries in a single worksheet.

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

merge excel files based on column

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


Download Practice Workbook


3 Ways to Merge Excel Files Based on Column

1. Using Excel VLOOKUP Function to Merge Files Based on Column

Applying VLOOKUP Function is a very effective way to merge Excel files based on a column. Here, we will bring the Salary column from the Merge Files (lookup) file and put it in the file named Merge Files. Let’s go through the procedure below.

Steps:

  • First, make a column for salaries in Merge Files and type the following formula in cell D5 of that file.
=VLOOKUP($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$C$11,2,FALSE)

merge excel files based on column

Here, the VLOOKUP Function looks for the value in cell B5, searches this value in range B5:C11 of the Merge Files (lookup) file (keep that in mind that we have to use Absolute Cell Reference) and returns 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 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.

  • After that, use the Fill Handle to AutoFill lower cells.

merge excel files based on column

Thus you can merge excel files based on a column by using the VLOOKUP Function.


2. Merging Excel Files Based on Column with INDEX and MATCH Functions

We can also use the combination of INDEX and MATCH Functions to merge Excel files based on a column. Here, we will bring the Salary column from the Merge Files (lookup) file and put it in the file named Merge Files. Let’s go through the procedure below.

Steps:

  • First, make a column for salaries in Merge Files and type the following formula in cell D5 of that file.
=INDEX('[Merge Files (lookup).xlsx]lookup'!$C$5:$C$11,MATCH($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$B$11,0))

Here, the MATCH Function looks up for 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 that 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.

merge excel files based on column

  • After that, use the Fill Handle to AutoFill lower cells.

Thus you can merge excel files based on a column by using the INDEX and MATCH Functions.


3. Applying Power Query Editor to Merge Excel Files Based on Column

If you find using formula(s) a little bit difficult, you can use the Power Query Editor from the Data Tab to merge files based on column. Just follow the process below.

Steps:

  • Open a new worksheet and select Data >> Get Data >> From FIle >> From Excel Workbook

merge excel files based on column

  • The Import Data window will appear, Select Merge File and Open

  • Then the Navigator window will show up. Select power query as we save the names and designations in this sheet of the file named Merge Files.
  • Select Load >> Load To

merge excel files based on column

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

This operation will add the power query sheet from the Merge File file in the Queries & Connections section.

merge excel files based on column

  • Then again select Data >> Get Data >> From FIle >> From Excel Workbook

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

merge excel files based on column

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

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

merge excel files based on column

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

  • Now, select to Data >> Get Data >> Combine Queries >> Merge

merge excel files based on column

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

merge excel files based on column

  • Click on the marked icon in salary column and select Salary.
  • Then click Ok.

You will see Name, Designation and  Salary together in Power Query Editor.

  • After that, select Close & Load.

merge excel files based on column

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

Thus, you can merge Excel files based on column by using Power Query Editor.


Practice Section

Here, I present to you the dataset of this article so that you can practice on your own.

merge excel files based on column


Conclusion

In the end, this article shows you some easy methods on how to merge Excel files based on column. If you enter the data manually, this will cost you a lot of time and inconvenience. That’s why we developed formula(s) and command to merge excel files based on column. If you have any better ideas or feedback, please share with me in the comment box. Your valuable thoughts will help me enrich my upcoming articles.

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo