On Sheet1 beginning in A1 I have these two student records
Jones Brian 10 Mr Boyle
Smith Joyce 11 Mr Newton
On Sheet 2 I have these two parent records also beginning in A1
Smith Albert 123-456-789 (e-mail address removed)
Jones Alice 902-555-1212 (e-mail address removed)
I have put them is different order to show the order is not an issue
In D1 of Sheet 1 I have entered the formula
=VLOOKUP($A1,Sheet2!$A$1:$D$100,1,FALSE)
In E2 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,2,FALSE)
Note how we go from 1 to 2 for the third arugment - this is the column we
are picking up
So in F1 and G1 I have
=VLOOKUP($A1,Sheet2!$A$1:$D$100,3,FALSE)
=VLOOKUP($A1,Sheet2!$A$1:$D$100,4,FALSE)
You can enter the first, drag across and edit to fix the third argument
(there is another way but not required with just 4 four fields)
Note the D100 --- I am pretending I have 100 parent records (make as big as
you need)
I selected D1:G1 on the student sheet and dragged down the row to get
Jones Brian 10 Mr Boyle Jones Alice 902-555-1212 (e-mail address removed)
Smith Joyce 11 Mr Newton Smith Albert 123-456-789 (e-mail address removed)
(Shame we get a scramble when we copy form Excel to email client - but on
the worksheet the fields align)
If the parents are in a different workBOOK, use something like
=VLOOKUP($A1,'[Parents 2009.xlsx]Sheet1'!$A$1:$D$100,1,FALSE)
and make sure that file is open
In the area I live many residents are descended from Scottish immigrants who
had to leave Scotland in the "clearances". So some 15% are called MacDonald
and other 5% are Chisholms. This method would fail locally!! You will
always get the first name that has a match in the two lists.
When I typed in something like (e-mail address removed) I got a mail_to hyperlink.
This is not preserved with the VLOOKUP
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
lmirrim said:
I have two worksheets: One with a students first and last name
and one with the parents first and last name. I want to link the
two worksheets together by matching the last names.
For example:
Student worksheet:
Last name, firstname, grade, teacher
Parent worksheet:
last name, first name, contact phone, email address
I want to create a master list that contains all of the above
fields on one worksheet. One record per student.
I know a way to do this in a program like SAS, but there must be some way
to do it simply in
Excel. Any ideas?