Need help with a look-up/reference/matching formula

[bfendel]

Hello -
Love the knowledge of excel masters in these forums...hoping someone can help me out with a formula that's spinning my head around.

Please see my attached spreadsheet for reference. I've created a simplified version of my actual data but my real spreadsheet is over 1200 rows.

The situation here is that a number of students (first names listed in column A) have given a rating from 1-9 (Column B) to their instructors (First and Last names in columns C&D).

In Columns E-K, I've added column headers of each student's name again. What I'd like, for E2:K39, is a formula that I can copy/drag, that does the following:
- identifies the Instructor's First and Last names in that row, and looks to see if the student named in the column header has given a rating to that instructor (either in that row or anywhere in the spreadsheet). If so, return that rating from column B into the column. If not, return nothing into that cell.

For example, the formula, for cell E2, would basically say "ok, I'm looking for a rating of Bill Jones, provided by Alice." Found it in B2, so I'll return a 7 into cell E2.

For J2, it would look for Anthony's rating of Bill Jones, but would not find one, so would leave the cell blank.

Then, I'd like some instructions on how I would best make a copy of the entire sheet (pasting only the values, not the formulas), so that I can delete all the duplicate instructor rows, since Bill Jones has 4 rows currently, but eventually I just want a single row per instructor with as many student ratings as he has.

It's important to verify that the instructors first and last names match, as I have many duplicate 1st names (as I've modeled here with John) and also many duplicate last names (not suggested in this dummy spreadsheet but present in my actual one.)

I appreciate the help! It would be super helpful to be able to paste the formula in a single cell and drag/copy it into all the others. Second best would be a formula for each cell in row 2, that I could copy all the way down the columns E-J.

Good luck!
Ben

[NeedForExcel]

Here!

Put this formula in Cell E2 and Drag Down & Across..

=IF(E$1=$A2,$B2,"")

Deep

[NeedForExcel]

Ooopsss Forgot to answer other Questions -

Then, I'd like some instructions on how I would best make a copy of the entire sheet (pasting only the values, not the formulas), so that I can delete all the duplicate instructor rows, since Bill Jones has 4 rows currently, but eventually I just want a single row per instructor with as many student ratings as he has.

Select the Whole Range and Follow the Steps -

Ctrl+C >> Ctrl+Alt+V >> V >> Enter >> Escape Key

Deep