ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Can vlookup be used to search in more than one worksheet? (https://www.excelbanter.com/excel-worksheet-functions/94525-can-vlookup-used-search-more-than-one-worksheet.html)

Rufus T Firefly

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.

Chip Pearson

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.




Peo Sjoblom

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.






Carlos Antenna

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.





All times are GMT +1. The time now is 12:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com