Thread: Combining vlookup with concatenate

I have a worksheet in which I want to add vlookup function where the table array will be taken dynamically from concatenated path.
I want to store the path in one cell, the filename prefix in another cell, the file name suffix in a 3rd cell and the sheet and range in a 4th cell.

How to do that?

Thanks

I succeed to get the full path by using the "&" sign or the CONCATENATE function, but doesn't succeed to use it as the table array of VLLOKUP function. For example I have the following table:
A | B | C | D | E
-------------------------------------------------------------
path | file_prefix | file_version | sheet_name | range
--------------------------------------------------------------
D:\Test\ | CP_Desc_ | 21 | process A | $A$4:$AA$202

I want to get the function:

=VLOOKUP($D24,'D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202,3,0)

by instead giving the explicit path: D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202 , concatenate it from the different cells. I tried:

=VLOOKUP($D26,CONCATENATE("'",A2,"[",B2,C2,".xls]",D2,"'","!",E2),3,0)

But I get #VALUE!.

What is the problem? any solution?

Thanks a lot

When I try the following:

=VLOOKUP($D25,$F$21,3,0)

While the cell F21 contains the full path range as formated in vlookup (the result of the concatenate function as desplayed in cell F21 is:

'D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202

But is still doesn't work. any idea?

Thanks

Oh, I understand, to use the function "INDIRECT".
When i try the following:

=VLOOKUP($D9,INDIRECT($F$21),3,0)

while the cell F21 holds the result of a concatenate function which is:
'D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202

I get the result: #REF!

What can be the reason. What is wrong?

Thanks

Originally Posted by Bryan Hessey

="'C:\Excel\[Book1.xls]Sheet1'!A1:D4"

with

=VLOOKUP($D9,INDIRECT($F$21),3,FALSE)

works if the book is open, #REF if closed.

hth
---

So I have been scouring blogs and maybe what I want to do is impossible.

What I want is exactly what this poster asked for.
Create a file name on the fly
vlookup information in that file.

The solution posted above was that you have to store the file name in an intermediate cell and then use indirect. Why that is, I have no idea. It sure seems that the original Concatenate solution should have worked.

Regardless, this only works if the file is open. Otherwise you get #REF errors.
Indirect does not work on closed files.

I have a years worth of files.
I do not/can not/ manually open them all before doing this operation.

So I need to expand the problem

Does anyone know how to

Create a file name on the fly
vlookup information in that file.
AND work on closed files.

Add 0 to the cell that you have performed the concatenate formula.
Then do the vlookup from the cell that you have added 0.