Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP problem | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |