Variable in VLOOKUP linked to separate workbook

R

raycyn.wright

I want to lookup a value in a table that resides in another workbook.
However, the col_index_num part of the VLOOKUP function will vary.



The table is called TABLEA, and each of the columns in TABLEA is headed by
the appropriate column number for a VLOOKUP function; each of these column
numbers has a label that varies only by the fiscal year it represents, e.g.,
GAFY2000Bud, GAFY2001Bud, GAFY2002Bud, etc.



In the workbook from which I am executing the VLOOKUP, I have a cell whose
contents reflect the fiscal year , e.g., FY2000, FY2001, etc. depending on
the situation.



I have searched the news group archives, and also tried several means of
using the INDIRECT function, but can't seem to get it right. I know that
use of the INDIRECT function may require that the source workbook be open.



Any solutions appreciated greatly,
 
D

Don Guillett

Try using match to find the column in the table. Something like this to
modify.
=VLOOKUP([MENU.xls]trythis!$D$1:$G$4,2,FALSE)
Click to expand...
=VLOOKUP([MENU.xls]trythis!$D$1:$G$4,match(b2,[yourwb.xls]yoursheet!$d$1:$F$
1),FALSE)
 
H

Harlan Grove

Don Guillett said:
=VLOOKUP([MENU.xls]trythis!$D$1:$G$4,
match(b2,[yourwb.xls]yoursheet!$d$1:$F$1),FALSE)
Click to expand...

Missing the first, lookup value argument, aren't you?
 
R

raycyn.wright

Thank you Don for once again helping me. Yours is an ingenious solution I
had not thought of. I also discovered that my solutions actually did work,
but that I had closed the source workbook, even thought I know that won't
work.

Thanks again,

Ray Wright

Don Guillett said:
Try using match to find the column in the table. Something like this to
modify.
=VLOOKUP([MENU.xls]trythis!$D$1:$G$4,2,FALSE)
Click to expand...
=VLOOKUP([MENU.xls]trythis!$D$1:$G$4,match(b2,[yourwb.xls]yoursheet!$d$1:$F$
1),FALSE)
--
Don Guillett
SalesAid Software
[email protected]
raycyn.wright said:
I want to lookup a value in a table that resides in another workbook.
However, the col_index_num part of the VLOOKUP function will vary.



The table is called TABLEA, and each of the columns in TABLEA is headed by
the appropriate column number for a VLOOKUP function; each of these column
numbers has a label that varies only by the fiscal year it represents, e.g.,
GAFY2000Bud, GAFY2001Bud, GAFY2002Bud, etc.



In the workbook from which I am executing the VLOOKUP, I have a cell whose
contents reflect the fiscal year , e.g., FY2000, FY2001, etc. depending on
the situation.



I have searched the news group archives, and also tried several means of
using the INDIRECT function, but can't seem to get it right. I know that
use of the INDIRECT function may require that the source workbook be open.



Any solutions appreciated greatly,
Click to expand...
Click to expand...
 
Top