It's rather complicated but it can be done
=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),2,0)
Where a list of the sheet names is named "MySheets" and it will allow a de
facto VLOOKUP over multiple sheets.
The formula needs to be entered with ctrl + shift & enter.
if one hard codes the names it can be entered normally
=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A2)0),0))&"'!A2:C200"),2,0)
example can be downloaded here
http://nwexcelsolutions.com/Download/3DVLOOKUP.xls
--
Regards,
Peo Sjoblom
Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
"Chip Pearson" wrote in message
...
You cannot have a VLOOKUP look in multiple sheets.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Rufus T Firefly" <Rufus T wrote in
message ...
I have a workbook containing 12 worksheets (1 per month), each contain a
table of 6 columns x 300 rows. There are drop downlists in three columns
and
i would like to create a printable report using vlookup to return data
into a
thirteenth worksheet.
So in short, I would like to enter a search criteria in sheet 13 that
searches the other 12 worksheets and returns the required value.
I know you can use vlookup to return values found in another worksheet
but i
cannot get the formula to work when asking the vlookup to search 12
sheets at
once.
Am I right in thinking it cannot be done?
Rufus T.