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