Lookup table_array
 

I'm not sure if this is possible but I would like to use a lookup function
but specify the table array based on certain criteria.
e.g. If sales part = "a" then use spreadsheet a.xls in the array.
I have thousands of parts so an 'if' function is not viable! I've tried to
use concatenation but this still doesn't work!
It seems like I may have to create a macro or a centralised lookup table for
this.
Any help or thoughts would be much appreciated.
Thanks,
James.