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

The article will provide you with some basic tips on how to merge Excel files based on a column. Sometimes, we may have different information about the same people or items in different Excel workbooks. Therefore, we may be required to merge that information into 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.

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

How to Merge Excel Files Based on Column: 3 Ways

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

Applying the VLOOKUP Function is one way to combine multiple workbooks into one workbook in Excel based on columns. 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.

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 in mind that we have to use 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.

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

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.

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.

• 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

• 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

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

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

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

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

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

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

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

• Click on the marked icon in the 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.

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

Thus, you can merge Excel files based on columns 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.

Download Practice Workbook

Conclusion

In the end, this article shows you some easy methods on how to merge Excel files based on columns. If you enter the data manually, this will cost you a lot of time and inconvenience. That’s why we developed formula(s) and a command to merge Excel files based on a 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.

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