Index,Match table array in separate workbook

[Guest]

Can the table array of an index/match type lookup reside in a separate
workbook without any problems

 

[Ardus Petus]

Yes it can: no problem!

Didn't you try?

HTH

 

[Guest]

I did but I exprienced some problems with the links. Now that I know it's OK
I'll try again But I have a further question please. Can the name of the
other workbook reside in a cell in the workbook that contains the lookup
formula. If so could you please post an example of a simple Vlookup
substituting the Table Array with a reference to a cell that contains either
the full path of the array or maybe just the name of the other workbook. I
have experimented with it but I have not been successful so far.
Thanks

 

[Guest]

Here is what I mean

=VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6)

I'd like the reference to the other workbook Copper.xls to reside in a cell
a5. How should I then express this formula.

Thanks

 

[Ardus Petus]

=VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE)

You probably forgot to specify VLOOKUP 4th parameter.

HTH
--
AP

Here is what I mean

=VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6)

I'd like the reference to the other workbook Copper.xls to reside in a
cell
a5. How should I then express this formula.

Thanks

I did but I exprienced some problems with the links. Now that I know it's
OK
I'll try again But I have a further question please. Can the name of the
other workbook reside in a cell in the workbook that contains the lookup
formula. If so could you please post an example of a simple Vlookup
substituting the Table Array with a reference to a cell that contains
either
the full path of the array or maybe just the name of the other workbook.
I
have experimented with it but I have not been successful so far.
Thanks

 

[Guest]

Thanks, that helped but for the benefit of future readers it did not work
first time. I think there may be 2 slight inaccuracies in the formula
(there's a qoute mark missing after1892 and I believe the last semi colon
should be a comma. The syntax below works fine.
Thanks for your help.

=VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892"),6,FALSE)

=VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE)

You probably forgot to specify VLOOKUP 4th parameter.

HTH
--
AP

Here is what I mean

=VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6)

I'd like the reference to the other workbook Copper.xls to reside in a
cell
a5. How should I then express this formula.

Thanks

I did but I exprienced some problems with the links. Now that I know it's
OK
I'll try again But I have a further question please. Can the name of the
other workbook reside in a cell in the workbook that contains the lookup
formula. If so could you please post an example of a simple Vlookup
substituting the Table Array with a reference to a cell that contains
either
the full path of the array or maybe just the name of the other workbook.
I
have experimented with it but I have not been successful so far.
Thanks
:

Yes it can: no problem!

Didn't you try?

HTH
--
AP

"Ben" <[email protected]> a écrit dans le message de (e-mail address removed)...
Can the table array of an index/match type lookup reside in a
separate
workbook without any problems

 

[Ardus Petus]

Thanks for the feedback

I answered your question without testing.
Semi-colon is my french locale separator.

Cheers,
--
AP

Thanks, that helped but for the benefit of future readers it did not work
first time. I think there may be 2 slight inaccuracies in the formula
(there's a qoute mark missing after1892 and I believe the last semi colon
should be a comma. The syntax below works fine.
Thanks for your help.

=VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892"),6,FALSE)

=VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE)

You probably forgot to specify VLOOKUP 4th parameter.

HTH
--
AP

Here is what I mean

=VLOOKUP(M5,[Copper.xls]Prices!$B$3:$Z$1892,6)

I'd like the reference to the other workbook Copper.xls to reside in a
cell
a5. How should I then express this formula.

Thanks


:

I did but I exprienced some problems with the links. Now that I know
it's
OK
I'll try again But I have a further question please. Can the name of
the
other workbook reside in a cell in the workbook that contains the
lookup
formula. If so could you please post an example of a simple Vlookup
substituting the Table Array with a reference to a cell that contains
either
the full path of the array or maybe just the name of the other
workbook.
I
have experimented with it but I have not been successful so far.
Thanks
:

Yes it can: no problem!

Didn't you try?

HTH
--
AP

"Ben" <[email protected]> a écrit dans le message de
(e-mail address removed)...
Can the table array of an index/match type lookup reside in a
separate
workbook without any problems