Merge two excel files using a common column
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.
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.
Note the contents of Sheet2:
When we fill the formula down, we get matches on all values except for the "2" in cell A2.
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.
Final Result:
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 http://www.gridoc.com/join-tables and merge the sheets using drag&drop (Disclaimer: I am author of the tool).
Hope this helps.