Index,Match table array in separate workbook

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

Yes it can: no problem!

Didn't you try?

HTH
--
AP

"Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
> Can the table array of an index/match type lookup reside in a separate
> workbook without any problems

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
"Ardus Petus" wrote:

> Yes it can: no problem!
>
> Didn't you try?
>
> HTH
> --
> AP
>
> "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
> 9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
> > Can the table array of an index/match type lookup reside in a separate
> > workbook without any problems
>
>
>

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


"Ben" wrote:

> 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
> "Ardus Petus" wrote:
>
> > Yes it can: no problem!
> >
> > Didn't you try?
> >
> > HTH
> > --
> > AP
> >
> > "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
> > 9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
> > > Can the table array of an index/match type lookup reside in a separate
> > > workbook without any problems
> >
> >
> >

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

You probably forgot to specify VLOOKUP 4th parameter.

HTH
--
AP

"Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
78BE50C0-7726-4971-8FE1-8DE1527DCF57@microsoft.com...
> 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
>
>
> "Ben" wrote:
>
>> 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
>> "Ardus Petus" wrote:
>>
>> > Yes it can: no problem!
>> >
>> > Didn't you try?
>> >
>> > HTH
>> > --
>> > AP
>> >
>> > "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
>> > 9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
>> > > Can the table array of an index/match type lookup reside in a
>> > > separate
>> > > workbook without any problems
>> >
>> >
>> >

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)


"Ardus Petus" wrote:

> =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE)
>
> You probably forgot to specify VLOOKUP 4th parameter.
>
> HTH
> --
> AP
>
> "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
> 78BE50C0-7726-4971-8FE1-8DE1527DCF57@microsoft.com...
> > 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
> >
> >
> > "Ben" wrote:
> >
> >> 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
> >> "Ardus Petus" wrote:
> >>
> >> > Yes it can: no problem!
> >> >
> >> > Didn't you try?
> >> >
> >> > HTH
> >> > --
> >> > AP
> >> >
> >> > "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
> >> > 9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
> >> > > Can the table array of an index/match type lookup reside in a
> >> > > separate
> >> > > workbook without any problems
> >> >
> >> >
> >> >
>
>
>

Thanks for the feedback

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

Cheers,
--
AP

"Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
A4D3D7D7-F25D-474B-A9CA-7A562E58728C@microsoft.com...
> 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)
>
>
> "Ardus Petus" wrote:
>
>> =VLOOKUP(M5,INDIRECT("["&A5&"]Prices!$B$3:$Z$1892),6;FALSE)
>>
>> You probably forgot to specify VLOOKUP 4th parameter.
>>
>> HTH
>> --
>> AP
>>
>> "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de news:
>> 78BE50C0-7726-4971-8FE1-8DE1527DCF57@microsoft.com...
>> > 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
>> >
>> >
>> > "Ben" wrote:
>> >
>> >> 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
>> >> "Ardus Petus" wrote:
>> >>
>> >> > Yes it can: no problem!
>> >> >
>> >> > Didn't you try?
>> >> >
>> >> > HTH
>> >> > --
>> >> > AP
>> >> >
>> >> > "Ben" <Ben@discussions.microsoft.com> a écrit dans le message de
>> >> > news:
>> >> > 9D0BB3C0-C52A-4744-88B7-12F26817D167@microsoft.com...
>> >> > > Can the table array of an index/match type lookup reside in a
>> >> > > separate
>> >> > > workbook without any problems
>> >> >
>> >> >
>> >> >
>>
>>
>>