Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can vlookup be used to search in more than one worksheet?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can vlookup be used to search in more than one worksheet?
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can vlookup be used to search in more than one worksheet?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with variable worksheet reference | Excel Worksheet Functions | |||
Updating formula with link to another worksheet using vlookup | Links and Linking in Excel | |||
Vlookup reference a worksheet | Excel Worksheet Functions | |||
Vlookup with 2 ranges in one worksheet | Excel Worksheet Functions | |||
How to use a cell value as Table Array in VLOOKUP worksheet function | Excel Discussion (Misc queries) |