Dynamic VLOOKUP table_array value

[jbodell]

Hello,

Here's my situation- I've got a VLOOKUP set up in column D which references the (row based) value in Column A as the lookup_value, where Columns B and C are the table_array (returning C exactly) e.g. in D1:

=vlookup(A1,B:C,2,false). This works as it should.

Column C contains the text string ="'[Workbook.xlsm]Worksheet'!A:Y" which is intended to be substituted as a table_array for a separate VLOOKUP in E which is:

=vlookup(A1,text(D1),25,false). This breaks. I have included the full file path and checked it enough to make me cross-eyed. When I manually insert the string, the VLOOKUP returns a value successfully, but in trying to add the layer of sophistication it refuses to function.

I've tried variations of text(), value() and separated the file path/name with "A:Y" tacked on by an &.

Has anyone smarter than me successfully pulled this off? Or can you point me in the right direction? I have a lot to learn when it comes to data-linking.

Thank you,

Jonathan

 

[VoG]

Perhaps

=VLOOKUP(A1,INDIRECT(D1),25,FALSE)

 

[jbodell]

VoG you're the man- that did the trick. Thank you!