VLOOKUP with indirect references?

Hello!

At present, our students fill out the instructor and date taught in their own gradebooks when they receive an academic lesson.

However, I would like to change the system so that the instructor fills out an attendance sheet, and the lesson instructor name and date taught are automatically put in the student gradebooks via VLOOKUP.

I have attached sample workbooks. Class.xlsx contains the student names and their student numbers. Note that the student gradebook files names are a concatenation of their name and student number, i.e. John 123.xlsx. The instructor will log his name and the date of the lesson he teaches in Attendance.xlsx. The student gradebooks (John 123.xlsx, Mary 124.xlsx and Steve 125.xlsx) will VLOOKUP the instructor name and date taught from appropriate worksheet in Attendance.xlsx.

I have manually inputted the instructor and date for academic lesson PB-010 manually. There will be a separate worksheet for each academic lesson (PB-020 and PB-030 in the attached sample).

I could do manual VLOOKUP, but I would like to use INDIRECT as much as possible to make it easy to expand the lesson recording system.

Thanks in anticipation!