Thread: VLOOKUP across all sheets in a workbook

what is need is something like:

=VLOOKUP(A2;'Sheet1:Sheet2'!A:G;5;FALSE)

but of course it will not work in such way. I jsut need the vlookup to
search not in one but ALL sheets and there are around a 1000, so instead of
repeating the function =VLOOKUP(A2;Sheet(1)!A:G;5;FALSE) on each sheet i
would like the function to go through all sheets at once)

Best Regards,

Pawel P.

(X) is the sheet number :-)

Pawel P. wrote...
>what is need is something like:
>
>=VLOOKUP(A2;'Sheet1:Sheet2'!A:G;5;FALSE)
>
>but of course it will not work in such way. I jsut need the vlookup to

>search not in one but ALL sheets and there are around a 1000, so
instead of
>repeating the function =VLOOKUP(A2;Sheet(1)!A:G;5;FALSE) on each sheet
i
>would like the function to go through all sheets at once)

You have 1000 or so worksheets each with 65536 rows of data in columns
A through G? Even if each cell ate just one byte of memory, this would
require over 450 million bytes. With numeric data, stored as 8-byte
reals, you'd blow through real RAM and go well into virtual memory on
most current systems. A single such formula would take a long time to
calculate.

If you really do have this much data, you NEED a database, and you're
begging for BIG TROUBLE using Excel (or any other spreadsheet).

However, there is a way to do this (but it'll be SLOW if you really do
have this much data). Create a list of worksheet names (I'll refer to
it as WSLst). Then use the array formula

=VLOOKUP(A2;INDIRECT("'"&INDEX(WSLst;MATCH(1;
COUNTIF(INDIRECT("'"&WSLst&"'!A:A");A2);0))&"'!A:G");5;0)

Harlan


sssshhhhh, you might get Aaron back ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


<hrlngrv@aol.com> wrote in message
news:1105572366.549345.131150@f14g2000cwb.googlegroups.com...
> Pawel P. wrote...
>>what is need is something like:
>>
>>=VLOOKUP(A2;'Sheet1:Sheet2'!A:G;5;FALSE)
>>
>>but of course it will not work in such way. I jsut need the vlookup to
>
>>search not in one but ALL sheets and there are around a 1000, so
> instead of
>>repeating the function =VLOOKUP(A2;Sheet(1)!A:G;5;FALSE) on each sheet
> i
>>would like the function to go through all sheets at once)
>
> You have 1000 or so worksheets each with 65536 rows of data in columns
> A through G? Even if each cell ate just one byte of memory, this would
> require over 450 million bytes. With numeric data, stored as 8-byte
> reals, you'd blow through real RAM and go well into virtual memory on
> most current systems. A single such formula would take a long time to
> calculate.
>
> If you really do have this much data, you NEED a database, and you're
> begging for BIG TROUBLE using Excel (or any other spreadsheet).
>
> However, there is a way to do this (but it'll be SLOW if you really do
> have this much data). Create a list of worksheet names (I'll refer to
> it as WSLst). Then use the array formula
>
> =VLOOKUP(A2;INDIRECT("'"&INDEX(WSLst;MATCH(1;
> COUNTIF(INDIRECT("'"&WSLst&"'!A:A");A2);0))&"'!A:G");5;0)
>

Nick Hodge wrote...
>sssshhhhh, you might get Aaron back ;-)
....

Different newsgroup. You're giving the troll too much credit.