Vlookup using a cell containing a reference to closed workbook array

[Fredek]

Hi all,

I am trying to do the below VLOOKUP, however I am getting an error. The Source Workbook is closed and I would like array to be a cell in my workbook (G1 in this example).

Anyone have any idea if this is possible in Excel? Any help would be greatly appreciated!



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Frog[/TD]
[TD]=vlookup(A1,G1,2,false)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'C:\Users\Fred\Desktop\[Source.xlsx]Sheet1'!$A:$H[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

You would need to use =vlookup(A1, indirect(G1), 2, false), except that Indirect doesn't work with closed workbooks.

There is an add-in (Morefunc.xll) that has a function INDIRECT.EXT that does this, but I've not used it.

Doing full-column references like that is going to be dog-slow.

 

[Fredek]

That's what I feared! Thank you very much for your quick response.

 

[shg]

You're welcome.