vlookup formula

[West]

Thanks Niek for directing me to the INDIRECT function.

This works for my formula but now I have another problem in tha
INDIRECT requires all workbooks to be opened otherwise it returns
#REF.

I need a solution that can search through many files without having t
open them.

any other ideas?

Thanks in advance, West