43

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.

Indy
  • 533

2 Answers2

57

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

slachterman
  • 708
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
sevce
  • 249