VLOOKUP
 

Hi All,


Is it possible to do a VLOOKUP of a list that is 4 or 5 times as long
(273,616 rows long) as there are rows on an Excel sheet.

I could place each length of data on a seperate sheet in the same
wookbook, or I could place all of the dada on the same sheet.

Each length of data is 3 columns wide.

Thanks for your help.


Tom Snyder

You could use lots (4-5) =vlookup()'s in your formula:

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),VLOOKUP(A1,Sh eet2!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet3!A:A,0)),VLOOKUP(A1,She et3!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet4!A:A,0)),VLOOKUP(A1,She et4!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet5!A:A,0)),VLOOKUP(A1,She et5!A:C,2,FALSE),
"missing from all sheets"))))

(all one cell)

You are limited by 7 nested functions, though.

Another way:

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),"",VLOOKUP(A1, Sheet2!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet3!A:A,0)),"",VLOOKUP(A1, Sheet3!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet4!A:A,0)),"",VLOOKUP(A1, Sheet4!A:C,2,FALSE))
&IF(ISERROR(MATCH(A1,Sheet5!A:A,0)),"",VLOOKUP(A1, Sheet5!A:C,2,FALSE))

If it doesn't find the value on any sheet, it brings back "". If you're
returning a string, it might work for you.

If you're returning a numeric value:

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),0,VLOOKUP(A1,S heet2!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet3!A:A,0)),0,VLOOKUP(A1,S heet3!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet4!A:A,0)),0,VLOOKUP(A1,S heet4!A:C,2,FALSE))
+IF(ISERROR(MATCH(A1,Sheet5!A:A,0)),0,VLOOKUP(A1,S heet5!A:C,2,FALSE))


lehigh46 wrote:

Hi All,

Is it possible to do a VLOOKUP of a list that is 4 or 5 times as long
(273,616 rows long) as there are rows on an Excel sheet.

I could place each length of data on a seperate sheet in the same
wookbook, or I could place all of the dada on the same sheet.

Each length of data is 3 columns wide.

Thanks for your help.

Tom Snyder

--

Dave Peterson

Perhaps this way may be easier on the logic but it requires an AddIn to be
downloaded and installed. The MoreFunc AddIn has a function called THREED
which changes a 3D range into a 2D range.

Your formula could look like this
=VLOOKUP(A1,THREED(Sheet2:Sheet5!$A$1:$C$65536),3, FALSE)

The Addin can be found here http://xcell05.free.fr/

Cheers!

Richard

"lehigh46" wrote:

Hi All,


Is it possible to do a VLOOKUP of a list that is 4 or 5 times as long
(273,616 rows long) as there are rows on an Excel sheet.

I could place each length of data on a seperate sheet in the same
wookbook, or I could place all of the dada on the same sheet.

Each length of data is 3 columns wide.

Thanks for your help.


Tom Snyder

Ignore my response, I just remembered that THREED is limited to 65536 lines.

Ooops!

"Richard Reye" wrote:

Perhaps this way may be easier on the logic but it requires an AddIn to be
downloaded and installed. The MoreFunc AddIn has a function called THREED
which changes a 3D range into a 2D range.

Your formula could look like this
=VLOOKUP(A1,THREED(Sheet2:Sheet5!$A$1:$C$65536),3, FALSE)

The Addin can be found here http://xcell05.free.fr/

Cheers!

Richard

"lehigh46" wrote:

Hi All,


Is it possible to do a VLOOKUP of a list that is 4 or 5 times as long
(273,616 rows long) as there are rows on an Excel sheet.

I could place each length of data on a seperate sheet in the same
wookbook, or I could place all of the dada on the same sheet.

Each length of data is 3 columns wide.

Thanks for your help.


Tom Snyder

"Dave Peterson" wrote...
You could use lots (4-5) =vlookup()'s in your formula:

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),VLOOKUP(A1,S heet2!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet3!A:A,0)),VLOOKUP(A1,She et3!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet4!A:A,0)),VLOOKUP(A1,She et4!A:C,2,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet5!A:A,0)),VLOOKUP(A1,She et5!A:C,2,FALSE),
"missing from all sheets"))))

(all one cell)

You are limited by 7 nested functions, though.
....

A variation on this would allow searching through 65535 worksheets. Enter a
list of worksheets in a single column, multiple row range and name that
range WSLST. Then use the array formula

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&WSLST&"'!A:A") ,A1)),
VLOOKUP(A1,INDIRECT("'"&INDEX(WSLST,MATCH(TRUE,
COUNTIF(INDIRECT("'"&WSLST&"'!A:A"),A1)0,0))&"'!A :C"),2,0),"")