| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Dear Expert,
I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
With your lookup value(part number in C1) try with the below formula. The
sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Hi Jocab,
Thanks for help! Let me put it clearly about what I am looking at. 1. An excel file named "Parts.xls" contains 3 full sheets of data (column A = Part# and column B = Description) as a database. 2. Create new excelsheet contain 12000 rows of data (column A = Parts) and want to VLOOKUP Parts.xls (sheet 1, 2 and 3 namely PG1, PG2 & PG3) to get the Description on the column B. Currently I am using this formula but it only lookup to certain range:- =IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG1'!$A:$B,2,0)),IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG2'!$A:$B,2,0)),IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG3'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG3'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG2'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG1'!$A:$B,2,0)) Hope you could write again the formula to VLOOKUP and external excel spreadsheet. BR//nginhong "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Hi Jacob,
Thanks for help! Let me put it clearly about what I am looking at. 1. An excel file named "Parts.xls" contains 3 full sheets of data (column A = Part# and column B = Description) as a database. 2. Create new excelsheet contain 12000 rows of data (column A = Parts) and want to VLOOKUP Parts.xls (sheet 1, 2 and 3 namely PG1, PG2 & PG3) to get the Description on the column B. Currently I am using this formula but it only lookup to certain range:- =IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG1'!$A:$B,2,0)),IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG2'!$A:$B,2,0)),IF(ISNA(VLOOKUP(A2,'[Parts.xls]PG3'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG3'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG2'!$A:$B,2,0)),VLOOKUP(A2,'[Parts.xls]PG1'!$A:$B,2,0)) Hope you could write again the formula to VLOOKUP and external excel spreadsheet. BR//nginhong "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the
description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Hi Jacob,
The formula is working fine but it used up 100% processor speed. Do you think any other formula could reduce the risk of processor being used 100%? BR//nginhong "Jacob Skaria" wrote: The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
If you are worried about the speed try 3 lookups (each for each sheet)..
somthing like the below =IF(ISNA(LOOKUP1),IF(ISNA(LOOKUP2),IF(ISNA(LOOKUP3 ),"",LOOKUP3),LOOKUP2) ,LOOKUP1) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, The formula is working fine but it used up 100% processor speed. Do you think any other formula could reduce the risk of processor being used 100%? BR//nginhong "Jacob Skaria" wrote: The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Hi Jacob,
Appreciate if you could write the formula in detail. I will test and come back with result. Thanks & Regards, Ngin Hong "Jacob Skaria" wrote: If you are worried about the speed try 3 lookups (each for each sheet).. somthing like the below =IF(ISNA(LOOKUP1),IF(ISNA(LOOKUP2),IF(ISNA(LOOKUP3 ),"",LOOKUP3),LOOKUP2) ,LOOKUP1) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, The formula is working fine but it used up 100% processor speed. Do you think any other formula could reduce the risk of processor being used 100%? BR//nginhong "Jacob Skaria" wrote: The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Use the below formula
=IF(ISNA(VLOOKUP1),IF(ISNA(VLOOKUP2),IF(ISNA(VLOOK UP3),"",VLOOKUP3),VLOOKUP2),LOOKUP1) AND Replace VLOOKUP1 in the above formula with =VLOOKUP(A1,[Parts.xls]PG1!A:B,2,FALSE) Replace VLOOKUP2 with =VLOOKUP(A1,[Parts.xls]PG2!A:B,2,FALSE) Replace VLOOKUP1 with =VLOOKUP(A1,[Parts.xls]PG3!A:B,2,FALSE) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, Appreciate if you could write the formula in detail. I will test and come back with result. Thanks & Regards, Ngin Hong "Jacob Skaria" wrote: If you are worried about the speed try 3 lookups (each for each sheet).. somthing like the below =IF(ISNA(LOOKUP1),IF(ISNA(LOOKUP2),IF(ISNA(LOOKUP3 ),"",LOOKUP3),LOOKUP2) ,LOOKUP1) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, The formula is working fine but it used up 100% processor speed. Do you think any other formula could reduce the risk of processor being used 100%? BR//nginhong "Jacob Skaria" wrote: The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to VLOOKUP multiple sheets and each sheet have 65536 rows?
Hi Jacob,
The formula below works better. Thanks & regards, nginhong "Jacob Skaria" wrote: Use the below formula =IF(ISNA(VLOOKUP1),IF(ISNA(VLOOKUP2),IF(ISNA(VLOOK UP3),"",VLOOKUP3),VLOOKUP2),LOOKUP1) AND Replace VLOOKUP1 in the above formula with =VLOOKUP(A1,[Parts.xls]PG1!A:B,2,FALSE) Replace VLOOKUP2 with =VLOOKUP(A1,[Parts.xls]PG2!A:B,2,FALSE) Replace VLOOKUP1 with =VLOOKUP(A1,[Parts.xls]PG3!A:B,2,FALSE) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, Appreciate if you could write the formula in detail. I will test and come back with result. Thanks & Regards, Ngin Hong "Jacob Skaria" wrote: If you are worried about the speed try 3 lookups (each for each sheet).. somthing like the below =IF(ISNA(LOOKUP1),IF(ISNA(LOOKUP2),IF(ISNA(LOOKUP3 ),"",LOOKUP3),LOOKUP2) ,LOOKUP1) If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Hi Jacob, The formula is working fine but it used up 100% processor speed. Do you think any other formula could reduce the risk of processor being used 100%? BR//nginhong "Jacob Skaria" wrote: The below will lookup Parts.xls (Sheets PG1,PG2,PG3) and return the description of the part number mentioned in current sheet A1... =VLOOKUP(A1,INDIRECT("[Parts.xls]PG"&MATCH(TRUE,COUNTIF(INDIRECT("[Parts.xls]PG"&ROW(INDIRECT("1:3"))&"!A:A"),A1)0,0)&"!A:B"), 2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: With your lookup value(part number in C1) try with the below formula. The sheet names 'Sheet1' , 'Sheet2' and 'Sheet3' =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "nginhong" wrote: Dear Expert, I have a total of 190,000 rows of data split onto 3 spreadsheets. How to VLOOKUP all 3 spreadsheet? Example:- Column A is part number and B is Description. Part# Description 12345 A 12346 B 12347 C 12348 D 12349 E 12350 F 12351 G 12352 H 12353 I 12354 J 12355 K 12356 L 12357 M Your support is greatly appreciated. BR//nginhong |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| more than 65536 rows | Excel Discussion (Misc queries) | |||
| Auto link rows of information from multiple sheets to single sheet | Excel Discussion (Misc queries) | |||
| Can I put more than 65536 rows in one Excel sheet? | Excel Worksheet Functions | |||
| Why does rngDataSource.Rows.Count = 65536 when worksheet Rows=95? | Excel Discussion (Misc queries) | |||
| how can i increase number of rows beyone 65536 in excel sheet | Excel Discussion (Misc queries) | |||
Linear Mode
