Vlookup across multiple workbooks

I found a vba code for vlookup across multiple worksheets, but I can't find one across workbooks.

I am creating a tracking system for teachers to view the behavior points each student earns in his or her other periods.

Math is on one workbook (for the math teacher to enter)

English on one, science on one, social studies on one, sped on one, and specials on the last.

So I am working with 6 workbooks.

Let's say I want to find what the person in cell M6 got in 2nd period.

I need a formula like this: =vlookallworkbooks(M6, "D2:E40", 2, False, "Math 8.xls", "Reading 8.xls", "Science 8.xls.....")

Where the arguments are crieria, table range, col number, range lookup, array of workbooks.

All the workbooks are set up the same way, so period one is the same table range: A2:B40 and period two is in the same table range: D2:E40 etc.


Currently right now, I just set up a master workbook that has regular vlookup functions finding what all students received in math period one, reading period one, etc and then it goes to period two ect. Then on the teacher's last period roster, it has vlookup functions referencing the master workbook. And this work; the only thing is a teacher has to remember to open the master workbook for the changes to upload, so I want to get rid of the master workbook and instead use a VBA code across workbooks.

I have attached the 8th grade folder which shows you all the workbooks I am working with.