Using single cell reference as table array argument in Vlookup
 

Is it possible to use a cell reference (on the same worksheet as the
vlookup function) as the table array argument in the vlookup function
in place of an explicit table array argument?

I'm trying to build a simple reporting tool that would allow me to
specify the file location, file name, worksheet name and array, and
then have the vlookup function recognize these arguments as the "table
array".

In other words, Here's the typical vlookup:

VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE)

What I want to do is to be able to replace the "Sheet2!$A$!:$P$100"
part of the formula with reference to a single cell on the same sheet
as the Vlookup formula. This cell would contain the information on the
table array to use (using the concatenate formula or something
equivalent).

The situation is that I have several different versions of a very large
file with 50+ tabs. I need to be able to specify the file name and the
tab name in order to extract data from these files quickly. Something
more elegant than doing a find & replace each time.

Thanks in advance!

=VLOOKUP(D10,INDIRECT(A1),5,FALSE)

will work as long as the remote file is open.

Regards,
KL



"CornNiblet" wrote in message
oups.com...
Is it possible to use a cell reference (on the same worksheet as the
vlookup function) as the table array argument in the vlookup function
in place of an explicit table array argument?

I'm trying to build a simple reporting tool that would allow me to
specify the file location, file name, worksheet name and array, and
then have the vlookup function recognize these arguments as the "table
array".

In other words, Here's the typical vlookup:

VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE)

What I want to do is to be able to replace the "Sheet2!$A$!:$P$100"
part of the formula with reference to a single cell on the same sheet
as the Vlookup formula. This cell would contain the information on the
table array to use (using the concatenate formula or something
equivalent).

The situation is that I have several different versions of a very large
file with 50+ tabs. I need to be able to specify the file name and the
tab name in order to extract data from these files quickly. Something
more elegant than doing a find & replace each time.

Thanks in advance!

Thank you - this will save me a ton of time. Very much appreciated!!

Or use Index. =index(a1,1,1)


--
BlueDaze


------------------------------------------------------------------------
BlueDaze's Profile: http://www.excelforum.com/member.php...o&userid=27465
View this thread: http://www.excelforum.com/showthread...hreadid=469680