merge 2 spread sheets

  • Thread starter Thread starter Jeremy Schubert
  • Start date Start date
J

Jeremy Schubert

I have 2 spread sheets.
Spread sheet 1 has the following columns:
first_name last_name studentID# address
Spread sheet 2 has the following columns:
student ID# class1

Spread sheet 1 has all 500 students
Spread sheet 2 does not have all 500 students because some do not take
class1, just class2 and class3.

How can I merge them both into one master spread sheet without having to
manually match studentID# (so if a student does not have class1, a space
will just be left in the class1 cell)?

Thanks,
Jeremy
 
Assuming the two sheets are in the same file, you can use a formula
like this in Sheet1 to bring across the class information from Sheet2:

=IF(ISNA(VLOOKUP(C2,Sheet2!A:B,2,0)),"",VLOOKUP(C2,Sheet2!A:B,2,0))

I'm assuming you put this in row 2 of Sheet1 so that you can pick up
the StudentID# from column C.

Then you can just copy it down your 500 rows.

Hope this helps.

Pete
 
Thanks Pete, your solution seems very straight forward. Can you please
explain what the A:B,2,0 in the formula refers to?
 
First of all, C2 is the lookup value - you want to find a match
between this and the left-most column of the lookup table.

Sheet2!A:B defines the look up table to be used - columns A and B in
Sheet2. Full-column references can be used with VLOOKUP, but you could
have Sheet2!A$1:B$500 instead.

The next parameter, 2, is the column number in the table from which
the returned data is obtained - in this case you want to find a match
in column A and then return the corresponding value from column B.

The final parameter, 0 (or it could have been FALSE) means that you
want an exact match.

Hope this helps.

Pete
 
Back
Top