Cross referencing

[Guest]

I have a sheet that correspond students (Column A) to teachers (Column B).

On another sheet I have a list of items worked on by the students. This
sheet has the student's name in Column A. I would like for it to populate the
teacher's name in Column B.

So on the item sheet, if row 5 was done by student Bob, I want B5 to show
the name on Bob's teacher.

Thanks,

 

[Guest]

One way is via INDEX/MATCH

Assuming source data is in Sheet1
col A = students, col B = teachers

Then in the other sheet, assume students' names are listed in A2 down

Put in B2:
=IF(A2="","",INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)))
Copy down as far as required