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

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

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

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

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

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

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

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

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

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