Variable in VLOOKUP linked to separate workbook

  • Thread starter Thread starter raycyn.wright
  • Start date Start date
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,
 
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 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?
 
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
(e-mail address removed)
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...
 
duh.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Harlan Grove said:
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?
Click to expand...
 
Back
Top