Sorry for the long entry, I just want to provide all info.

I have created a vlookup formula with a dynamic table_array. It basically looks for a value (the number assigned to a piece of equipment) in cell C8 in a series of sheets (each named after the students who are checking out gear). If the formula doesn't find the number, it looks in the next sheet and the next until it finds it. It then returns the value I'm looking for--in this case, the name of a student who has checked out a piece of equipment, located in cell D8--to an inventory sheet named after the type of gear being checked out (Sleeping Bags, for example).

Here is the formula I would put on sheet "Sleeping Bags" in cell C3 when cell B3 = the sleeping bag number being looked up:

{=VLOOKUP($B3,INDIRECT("'"&INDEX(StudentList,(MATC H(1,--(COUNTIF(INDIRECT("'"&StudentList&"'!$c$8:$d$8"),$ B3)0),0)))&"'!$c$8:d$8"),2,FALSE)}

"StudentList" referes to a named range of students on sheet "Students". Each student has a sheet name that matches his or her name on the StudentList.

So far it works great. For example, on sheet "Smith, Johnny", I enter that he checked out Sleeping Bag #3 in C8, and then on the sheet titled "Sleeping Bags", the formula puts his name next to sleeping bag #3. Perfect.

The problem is that I have 11 inventories and 32 students. Too many sheets to keep it neat. I want to separate the inventory sheets from the student checkout sheets and have the vlookup search the new "Checkout" workbook. So now the formula becomes:

{=VLOOKUP($B3,INDIRECT("'"&INDEX('[Checkout.xlsx]Students'!StudentList,(MATCH(1,--(COUNTIF(INDIRECT("'"&'[Checkout.xlsx]Students'!StudentList&"'!$c$8:$d$8"),$B3)0),0)))& "'!$c$8:d$8"),2,FALSE)}

When shft+cntrl+enter the formula, I get #n/a.

Any ideas?