merge 2 spread sheets

[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

 

[Pete_UK]

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

 

[Jeremy Schubert]

Thanks Pete, your solution seems very straight forward. Can you please
explain what the A:B,2,0 in the formula refers to?

 

[Pete_UK]

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