How to VLOOKUP multiple sheets and each sheet have 65536 rows?

N

nginhong

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
 
J

Jacob Skaria

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(INDIRECT("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
 
N

nginhong

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
 
N

nginhong

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
 
J

Jacob Skaria

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
 
N

nginhong

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 said:
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 said:
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(INDIRECT("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
Click to expand...
Click to expand...
 
J

Jacob Skaria

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 said:
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 said:
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 said:
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(INDIRECT("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


:

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
Click to expand...
Click to expand...
Click to expand...
 
N

nginhong

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 said:
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 said:
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 said:
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


:

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(INDIRECT("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


:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jacob Skaria

Use the below formula
=IF(ISNA(VLOOKUP1),IF(ISNA(VLOOKUP2),IF(ISNA(VLOOKUP3),"",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 said:
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 said:
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 said:
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

:

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


:

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(INDIRECT("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


:

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
Click to expand...
Click to expand...
Click to expand...
 
N

nginhong

Hi Jacob,

The formula below works better.

Thanks & regards,
nginhong

Jacob Skaria said:
Use the below formula
=IF(ISNA(VLOOKUP1),IF(ISNA(VLOOKUP2),IF(ISNA(VLOOKUP3),"",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 said:
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 said:
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


:

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

:

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


:

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(INDIRECT("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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Top