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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rufus T Firefly - 17 Jun 2006 01:18 GMT

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 to this Message

Chip Pearson - 17 Jun 2006 01:42 GMT

You cannot have a VLOOKUP look in multiple sheets.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

>I have a workbook containing 12 worksheets (1 per month), each
>contain a
[quoted text clipped - 18 lines]
>
> Rufus T.

Reply to this Message

Peo Sjoblom - 17 Jun 2006 02:46 GMT

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

Signature

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

> You cannot have a VLOOKUP look in multiple sheets.
>
[quoted text clipped - 17 lines]
>>
>> Rufus T.

Reply to this Message

Carlos Antenna - 17 Jun 2006 03:22 GMT

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

>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
[quoted text clipped - 15 lines]
>
> Rufus T.

Reply to this Message