Max, I'm wondering if you did a full application test on this. (I didn't
look at your sample file)

6 sheets with 65536 rows of data, 3000 rows of lookup values and 6 columns
of formulas.

What kind of calc time did that take?

Biff

"Max" wrote in message
...
Perhaps one play to try ..

Sample construct available at:
http://cjoint.com/?bfl6QazB5P
VLookUp_6Sheets_joe_d_builder.xls

Assume data in the 6 sheets are in cols A and B, from row1 down
(key col = col A, "city" in col B)

Rename the 6 sheets to be simply the numbers: 1,2,3,4,5,6
(The renaming of the sheetnames to the numbers 1 - 6
is to allow us to easily fill the extract formulas in Master)

Then in sheet: Master
where the data is in cols A to C, with the key col = col A
aaa bob 123
bbb june 345
etc

Put in D1, copy across 6 cols to I1, fill down as far as required:
=IF($A1="","",IF(ISNA(MATCH($A1,INDIRECT("'"&COLUM N(A1)&"'!A:A"),0)),"NO
ENTRY",VLOOKUP($A1,INDIRECT("'"&COLUMN(A1)&"'!A:B" ),2,0)))

Cols D to I will extract the "city" returns from each of the 6 sheets (1 -
6)
[ Col D = returns from sheet: 1, .. col I = returns from sheet: 6 ]
Unmatched cases will return "NO ENTRY"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--