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