Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 7 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.

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

share|improve this answer
    
Thank you very much..im 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.