Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rufus T Firefly
 
Posts: n/a
Default 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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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.





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Carlos Antenna
 
Posts: n/a
Default Can vlookup be used to search in more than one worksheet?

You can look in more than one sheet by nesting IF statements and testing for
errors, but IFs can only be nested to seven levels. This is no help in your
present situation but for future reference you should get the idea from
this:

=IF(isna(vlookup(sheet1 range)),IF(isna(vlookup(sheet2
range),"",vlookup(sheet1 range)))

--
Carlos

"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup with variable worksheet reference trempnvt Excel Worksheet Functions 6 May 23rd 06 06:47 PM
Updating formula with link to another worksheet using vlookup Matt Links and Linking in Excel 3 August 12th 05 01:04 PM
Vlookup reference a worksheet Gary Brown Excel Worksheet Functions 1 May 20th 05 07:17 PM
Vlookup with 2 ranges in one worksheet Eelco Wiertsema Excel Worksheet Functions 5 February 18th 05 08:57 PM
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 03:47 AM


All times are GMT +1. The time now is 01:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"