Lookup fn using a dynamic filename.

[Guest]

I am trying to use lookup on a dynamic file list.
I have a number of files in a directory, I want to do a lookup in.

So,
lookup(1,'[File1]8D Form'!$b3:$b1030,'[File1]8D Form'!$c3:$c1030)

lookup(1,'[File2]8D Form'!$b3:$b1030,'[File2]8D Form'!$c3:$c1030)

Where file1, file2 etc come from a directory list.

I can get file1 and file2 etc easily. What I can’t seem to do is dynamically create the lookup command.
I can create the text of the lookup function, I just cant tell it it’s a function and not text!


Any help would be most appreciated!

BJ