variable in a vlookup formula

Pirrbe

New Member
Joined
Oct 25, 2006
Messages
6
example : =vlookup(A1;[200601]rates!$A1:$B20;2)
Problem : now I have to use the 'replace' function to change 200601 in 200602. Time consuming as this kind of 'replace' is to execute several times on more complexe vlookup formulas.
I tried to replace the 200601 by a variable that contains the YYYYMM (as cell headers). But when I use this in a vlookup formula, the vlookup act as a 'text string' and loss his functionality.
So my question : is there no way to use 'variables' in a vlookup formula ?
(variable is by example cell A2 where A2 contains a YYYYMM)
Excel version : 2003
Regards, Pirrbe
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
The INDIRECT function can be used...

=VLOOKUP(A1;INDIRECT("'["&A2&"]rates'!A1:B20");2)

Hope this helps!
 

Pirrbe

New Member
Joined
Oct 25, 2006
Messages
6
Hi Domenic, your solution with the INDIRECT function works but... only if the second workbook is open ??. When I close the workbook/sheet on which the 'indirect' variable refers to, then I got #REF# errors. When I open that second workbook then all the vlookups are executed and get the correct values. I gonna play around this weekend with that INDIRECT function. It must work :). So thank you very much for this suggestion. Of course extra help is still welcome.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi Domenic, your solution with the INDIRECT function works but... only if the second workbook is open ??. When I close the workbook/sheet on which the 'indirect' variable refers to, then I got #REF# errors. When I open that second workbook then all the vlookups are executed and get the correct values. I gonna play around this weekend with that INDIRECT function. It must work :). So thank you very much for this suggestion. Of course extra help is still welcome.

Switch to INDIRECT.EXT if you want to work with closed books... It requires that you dowload and install the morefunc.xll add-in:

=VLOOKUP(A1;INDIRECT.EXT("'["&A2&"]rates'!A1:B20");2)
 

Pirrbe

New Member
Joined
Oct 25, 2006
Messages
6

ADVERTISEMENT

I installed the morefunc.xll and indeed, the 'INDIRECT.EXT' works. But still a problem : if both excel sheets are in same mainfolder (d:\excel) NO problem. The vlookup finds the correct values. But if I move the 'source' sheet into another folder (d:\excel\data) then it doesn'work. Even after popup windows to correct links... So the problem is still there : I still wonder if you realy can put in a variable the combination [path\workbook].....
example : =vlookup(A1:'D:\excel\data\[source.xls]source'!$A1:$B2;2)
So trying to have d:\excel\data\[source.xls] in a variable and putting it in a indirect.ext doesn't work...
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
Try...

=VLOOKUP(A1;INDIRECT.EXT("'D:\excel\data\["&A2&"]source'!A1:B20");2)

...where A2 contains the filename. Or, if you prefer...

=VLOOKUP(A1;INDIRECT.EXT("'"&A2&"source'!A1:B20");2)

...where A2 contains the path and filename, such as D:\excel\data\[source.xls].

Hope this helps!
 

Pirrbe

New Member
Joined
Oct 25, 2006
Messages
6
Aladin & Dominic, tnx for the tips. IT WORKS. Not only refering the path\workbook but also path\workbook\filename in combination with the INDIRECT.EXT to external books works perfect. So thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,920
Messages
5,545,029
Members
410,647
Latest member
bernardazar
Top