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