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
HIS


wrote in message
oups.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.