copy cells between sheets in excel based on data

[hobey19]

i have two sheets in excel with common information (ie First and Last names). Sheet 1 contains Last Name, First Name and Age. Sheet 2 contains Name of School, Last Name, First Name and Phone Number. is there any easy way to have it compare the names in each of the two sheets and put corresponding Schools and Phone numbers in Sheet 1? there is a lot more data in Sheet 2 than i need, so straight copy/paste is out of the question. thanks in advance

craig

[homer15]

i don't know how your spreadsheet is laid out, so use this as your key
sheet1
a1= last name
b1= first name
c1= age
d1=(column to be added later)
e1=(column to be added later)

sheet2
a1= (column to be added later)
b1=school
c1=last name
d1=first name
e1=phone number


if you add a column to the left of school on sheet2, have that cell have the following formula:
=c1&d1
copy that formula all the way down to the last cell on the last row.

then, back to your sheet1, use the following forumla:

for school name, click on cell d1, and use the following formula
=vlookup(a1&b1,sheet2:A:Z,2)

for school name, click on cell e1, and use the following formula
=vlookup(a1&b1,sheet2:A:Z,5)

[hobey19]

thanks homer. just out of curiosity, what will this do if there are multiple people with the same name (ie John Taylor)

edit: i tried it and it didn't work, i get a #NAME error.

my setup, if it makes a difference-
Sheet 1 - Columns
A - Last Name
B - First Name
C - Age
D - Phone
E - School

Sheet 2 - Columns (only pertinent letters shown below)
A - first&last (as per your instructions)
B - School
D - Last Name
E - First Name
N - Phone

thanks
craig

[homer15]

it'll find the first one on the list it comes across.

one way to check for potential problems like that is to use this formula:

=countif(a:a,a1)

(column a being the column we created on sheet2). if you copy that for every row, it'll tell you when there is more than one of that name combination (it'll show up as a number greater than 1). maybe you could deal with those on an individual basis if there aren't too many of them.

[hobey19]

alright, i have been trying a couple of different things, and have found that the data is pretty bad. there are tons of missing values and so i am just going through a copy/pasting. most of the information is spread through 3 files, so i am hoping whatever i don't find in one, i will find in the other two. thanks homer, for your help

craig