VLOOKUP WITH INDIRECT

[excelent]

Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell B5
this is what i got but it wont work
Can sombody pls. help

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"&B5),2)

 

[T. Valko]

INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:


http://xcell05.free.fr/morefunc/english/index.htm


It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.

 

[excelent]

ok thanks ill try that


"T. Valko" skrev:

INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:


http://xcell05.free.fr/morefunc/english/index.htm


It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.



--
Biff
Microsoft Excel MVP

Hi experts:

Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)

Then ill have a dynamic range insted of R15000 then use a number in cell
B5
this is what i got but it wont work
Can sombody pls. help

=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"&B5),2)

 

[Pete_UK]

You might like to try it like this:

=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B:R,2)

i.e. using full column references, so you don't need to specify the
bottom row, and hence don't need to use INDIRECT. Actually, your range
only needs to be B:C as you are taking the data from the second column
of the lookup table.

Hope this helps.

Pete

ok thanks ill try that

"T. Valko" skrev:

INDIRECT won't work on closed files.

The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:

It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.

Hi experts:
Cant remember how to combine Vlooup with Indirect
This 1 works ok
=VLOOKUP(B3,'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R15000,2)
Then ill have a dynamic range insted of R15000 then use a number in cell
B5
this is what i got but it wont work
Can sombody pls. help
=VLOOKUP(B3,INDIRECT("'C:\Users\pm\Desktop\[Varelager_XAL.xls]Sheet1'!B2:R"­&B5),2)- Hide quoted text -

- Show quoted text -