I have it working as expected right now, but it's really sloppy and long. There has got to be a way to clean this up. I've thought about the INDIRECT formula, but the files can rarely be open at the same time. I've tried converting the cell to a string using Right(A1,LEN(A1), but I don't know how to put that into the filename reference. Anyway, here is what I have going for me:

IF($A3="Mike",VLOOKUP($C3,'M:\Inventory Value Project\Individual Project Files\[Mike.xlsx]User Input'!$C:$M,9,FALSE),IF($A3="Matt",VLOOKUP($C3,'M:\Inventory Value Project\Individual Project Files\[Matt.xlsx]User Input'!$C:$M,9,FALSE),""))
That is a sample. There are a total of 6 or seven files, so this formula is too long and confusing to read. There has got to be a way to clean this up by doing something like '["&A3&".xls] or similar - instead of all those nested if statements...

Thanks!