Using cell references with INDEX MATCH LOOKUP

[sparky2205]

Hi folks,
I have the following lookup:

=INDEX('[Form-0439 Vendor Performance Assessment 2016.xlsx]Revised'!$AU:$AU,MATCH(A:A,'[Form-0439 Vendor Performance Assessment 2016.xlsx]Revised'!$B:$B,0))

What I want is to change the lookup each year to reflect the year in the form name. I was thinking:

INDEX("'[Form-0439 Vendor Performance Assessment " & A1 & ".xlsx]Revised'!$AU:$AU",MATCH(A:A,"'[Form-0439 Vendor Performance Assessment " & A1 & ".xlsx]Revised'!$B:$B",0))

Where A1 is the year.
I then change the value in A1 and the lookup updates accordingly.

It doesn't work though. Does it need a tweak or am I asking for something silly?

 

[sparky2205]

Hi folks,
I got this working. I had to use INDIRECT.

=INDEX(INDIRECT("'[Form-0439 Vendor Performance Assessment " & A1 & ".xlsx]Revised'!$AU:$AU",MATCH(A:A, INDIRECT("'[Form-0439 Vendor Performance Assessment " & A1 & ".xlsx]Revised'!$B$B",0))

Without INDIRECT I presume it was taking the form reference as a string rather than a reference.