=INDEX(,INDIRECT("'["&B3&"]"&B4&"'!"&C3:C8),MATCH(D3,INDIRECT("'["&B3&"]"&B4&"'!"&A3:A8),0))

B3=scores.xlsx
B4=(worksheet name)
C3:C8 should be on the scores.xlsx worksheet
D3=the data I am looking for on the scores.xlsx workbook.
A3:A8 should be on the scores.xlsx worksheet

Unfortunately it does not seem to be looking up the value I had expected it to, instead it is returning #VALUE! ...

Has anyone got any ideas as to why this is not working or another work around to the same effect.

I am trying to run an index match on another workbook on multiple worksheets. The worksheets are numbered 1 through to 30 for each day of the month. So it would be easier for me to INDIRECT for the worksheet names.

Thanks for the help!
Sam the Monster