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

[Rufus T Firefly]

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.

[Chip Pearson]

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 [email protected]>
wrote in message
news:[email protected]...
>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.

[Peo Sjoblom]

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";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2: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" <[email protected]> wrote in message
news:%[email protected]...
> 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 [email protected]> wrote in
> message news:[email protected]...
>>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.
>
>

[Carlos Antenna]

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 [email protected]> wrote in
message news:[email protected]...
>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.