Fame Burst

Home / general

Merge two excel files using a common column

Henry Morales |

I have two excel sheets. I have to merge the two such that the values in one match with the other. For eg.

The first excel, the 2nd excel
1 t 1 tes1
2 5 3 tes3
3 t 4 tes4
4 g

Notice that in the first column of the 2nd excel, 2 is missing, so I want the first excel to look like this,

1 tes1 t
2 5
3 tes3 t
4 tes4 g

I am new to excel. Any help on this will be highly appreciated.

2 Answers

I have placed the data from "the first excel" on Sheet1, and "the 2nd excel" on Sheet2.

The key to this solution is the VLOOKUP() function. First we insert a column.

Insert Column B

We then use the VLOOKUP() function to lookup the value of "1" in Sheet2. We specify 2 as the value of the third parameter, meaning we want the value of the 2nd column in the array. Also notice the use of the $ symbols to fix the array. This will be important when we fill down.

Vlookup()

Note the contents of Sheet2:

Sheet2

When we fill the formula down, we get matches on all values except for the "2" in cell A2. enter image description here

In order to display a blank ("") instead of "N/A", as in your problem statement, we can wrap the VLOOKUP() function in the IFERROR() function.

Replace N/A with blank

Final Result:

Final

3

You can also use Query from Excel Files :

  • Define name for the first excel table (Formulas tab -> Define name)
  • Define name for second excel table
  • Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
  • Select your workbook file and confirm that you want to merge the columns manually
  • In the following window "Query from Excel Files", drag&drop the first column of first table into the first column of second table - a link between these columns will be created
  • Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
  • Select the sheet into which you would like the matched data to be imported
  • Click OK -> you should see matched data with columns from both tables

Or if you don't mind uploading your files to an online service, you can use for example and merge the sheets using drag&drop (Disclaimer: I am author of the tool).

Hope this helps.

3

You Might Also Like