A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Can vlookup be used to search in more than one worksheet?



 
 
Thread Tools Display Modes
  #1  
Old June 17th 06, 01:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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.
Ads
  #3  
Old June 17th 06, 02:46 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old June 17th 06, 03:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07:57 PM
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 02:47 AM


All times are GMT +1. The time now is 09:17 AM.


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