Vlookup reference a worksheet

M

Mark Brown

I am attempting to use Vlookup to do a multiple lookup.

I have 7 different worksheets and would like the worksheet names to be one
variable and the worksheet to be a second variable, the vlookup formula is
below. $f$3 is a list of months. I would like to find a way to set the
'a26' to be a variable, so that I can look in different worksheets- depending
on what is input. Each worksheet is an individual person that I need to look
up data on.

=VLOOKUP($F$3,'a26'!$A$2:$J$3639,2,)
 
M

Mark Brown

Perfect. Thanks so much.

Duke Carey said:
This should do the trick - if you other workbooks are all open

=VLOOKUP($F$3,INDIRECT("'"&a26&"'!$A$2:$J$3639"),2,)
Click to expand...
 
J

Jim Thomlinson

That formula is great and should work just fine. There is one thing to note
however. Vlookup is a relatively slow function to execute and indirect is a
volitile function (calculates every time a calculation runs). Using the two
together means that the vlookup must recalculate every time and if you create
a whole bunch of these your performance will be adversly effected. I am not
saying don't do it. Just be aware of the possible performance issues.
 
J

Jeff Lowenstein

What is the syntax of the path name?

For instance, what would be the syntax for the file Text.xls located on the
desktop of user jeff ( windows 2000 or xp system)?

What would be there besides:
C:\DOCUMENTS AND SETTINGS\JEFF\DESKTOP\[TEST.XLS]
 
P

Peo Sjoblom

C:\Documents and Settings\Jeff\Desktop\[Test.xls]

so you have the correct path but unless the Test workbook is open you cannot
use INDIRECT
Btw, if you open Test.xls, copy a cell and paste special as link into
another workbook, if you close Text you'll get the full path, also

=CELL(Filename",A1)

will return path plus sheet name


--
Regards,

Peo Sjoblom

Portland, Oregon




Jeff Lowenstein said:
What is the syntax of the path name?

For instance, what would be the syntax for the file Text.xls located on
the
desktop of user jeff ( windows 2000 or xp system)?

What would be there besides:
C:\DOCUMENTS AND SETTINGS\JEFF\DESKTOP\[TEST.XLS]

Jim Thomlinson said:
That formula is great and should work just fine. There is one thing to
note
however. Vlookup is a relatively slow function to execute and indirect is
a
volitile function (calculates every time a calculation runs). Using the
two
together means that the vlookup must recalculate every time and if you
create
a whole bunch of these your performance will be adversly effected. I am
not
saying don't do it. Just be aware of the possible performance issues.
Click to expand...
Click to expand...
 
Top