combine 2 excel files based on a common name

[pinky8]

I have 2 files that are in excel 2007 format. File 1 has the name and age columns, with a total of 200 rows. File 2 has the name and country columns, with a total of 150 rows. Not all names in file 1 are in file 2. I want to combine the two files to have name, age, and country. The hard part is to combine the correct rows from the two files based on the common name. Is there a way to accomplish that? Thank you.

[Rebuild8]

If File1 has name in column A and age in column B, and File2 has name in column A and country in column B, here's what i would put in C1 of File1:
=IF(ISERROR(VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0)),"",VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0))
then copy down

[pinky8]

I put the formula in C1 but nothing happens.

[Rebuild8]

Change "File2" in both places to the name of the excel file that has the countries.

[pinky8]

I named the files as File1.xlsx and File2.xlsx to match your formula. For testing I have 2 rows in each file as follows.

File1.xlsx has:
A1=johnny B1=34
A2=lanny B2=24

File2.xlsx has:
A1=lanny B1=u.s.
A2=jim B2=u.k.

File1.xlsx:
C1 has:
=IF(ISERROR(VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0)),"",VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0))

It does not replace the formula with a value. I don't see a place to attach files when I reply to your message. Otherwise I'll attach the files.

[Rebuild8]

In the formula, replace "" with "no country" (include the quote marks). then copy down.

=IF(ISERROR(VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0)),"no country",VLOOKUP(A1,[File2.xlsx]Sheet1!$A$1:$B$200,2,0))

[pinky8]

Yeh... it works!
Why didn't it work before? If I don't want to the text "no country" and just want it to be blank, I should be able to specify ""?

[Rebuild8]

Yes. it probably didn't work before because the person in row 1 didn't have a country, and therefore returned "".

[pinky8]

Thank you very much for your help! I was searching for hours trying to find the solution but I got articles about merging or combining cells/workbooks/worksheets which did not solve the problem. Your solution is perfect.