Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 23 down vote accepted

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.


Note the contents of 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:


share|improve this answer
Thank you very a newbie in excel..i dont understand what you mean by sheet1 and sheet2? Are they 2 seperate files? It would be great if u could explain the steps in bit more detail, step by step...sorry im a complete beginner at this..once again I greatly appreciate ur help – Indy Dec 11 '11 at 1:46
Indy - Sheet1 and Sheet2 are just two "tabs" within the same workbook. I placed the sample data from the table labeled as "the 2nd excel" in your question on Sheet2, which is why the name of this sheet is referenced in the second parameter of the VLOOKUP() function. – slachterman Dec 11 '11 at 21:26

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.

share|improve this answer

You must log in to answer this question.

protected by Community Apr 17 at 22:48

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .