|
VLOOKUP on multiple sheets
Can VLOOKUP be used to search columns in multiple sheets in a workbook?
|
VLOOKUP on multiple sheets
Yes, if the sheets are identical to each other.
If you have the lookup value in A2 on a summary sheet and the sheets you want to lookup are Sheet1:Sheet8, the table is A1:C200 and you want to return the value in the second column (B) =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 1:A200"),A2)0),0))&"'!A1:C200"),2,0) entered with ctrl + shift & enter if you put all sheet names in a range of cells and give it a name it is less ugly =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A200"),A2)0) ,0))&"'!A1:C200"),2,0) where MySheets would hold the names -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Hi Peo,
WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
This is the formula that I would like to search on 8 different sheets. The
sheets will all be the same. The range to search is A1:E1000 on each sheet. =IF(ISERROR(VLOOKUP(A9,'[Okeene raw materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Okeene raw materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: Yes, if the sheets are identical to each other. If you have the lookup value in A2 on a summary sheet and the sheets you want to lookup are Sheet1:Sheet8, the table is A1:C200 and you want to return the value in the second column (B) =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 1:A200"),A2)0),0))&"'!A1:C200"),2,0) entered with ctrl + shift & enter if you put all sheet names in a range of cells and give it a name it is less ugly =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A200"),A2)0) ,0))&"'!A1:C200"),2,0) where MySheets would hold the names -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
On its way
Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Hmmmm,
Whatever this post has to do with anything...? sorry Howard "L. Howard Kittle" wrote in message ... Hi again Peo, Thanks "Peo Sjoblom" wrote in message ... Yes, if the sheets are identical to each other. If you have the lookup value in A2 on a summary sheet and the sheets you want to lookup are Sheet1:Sheet8, the table is A1:C200 and you want to return the value in the second column (B) =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 1:A200"),A2)0),0))&"'!A1:C200"),2,0) entered with ctrl + shift & enter if you put all sheet names in a range of cells and give it a name it is less ugly =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A200"),A2)0) ,0))&"'!A1:C200"),2,0) where MySheets would hold the names -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
I use the formula below to search 1 worksheet. What is the best way to be
able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Are your worksheets identical in layout like table construction where they
all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Yes. All sheets are identical.
"Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Hi Peo, Your formula appears to work without making it an array. Is the "entered with ctrl + shift & enter" instruction necessary? Peo Sjoblom Wrote: =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 1:A200"),A2)0),0))&"'!A1:C200"),2,0) entered with ctrl + shift & enter -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531235 |
VLOOKUP on multiple sheets
Hi John,
the hardcoded formula works but if you use =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),2,0) you need to array enter it. OTOH the one using a sheet list and a defined name is smaller and has better flexibility since you don't have to edit the formula if you add sheets -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "John James" wrote in message ... Hi Peo, Your formula appears to work without making it an array. Is the "entered with ctrl + shift & enter" instruction necessary? Peo Sjoblom Wrote: =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 1:A200"),A2)0),0))&"'!A1:C200"),2,0) entered with ctrl + shift & enter -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531235 |
VLOOKUP on multiple sheets
You can download an example here
http://nwexcelsolutions.com/Download/3DVLOOKUP.xls adapt it to fit your needs -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Yes. All sheets are identical. "Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
I still haven't quite got it yet. I have named a range with the worksheet
names included called "Catagories" in a workbook called "Ingredients Spanish Fork". The Vlookup formula is in a different workbook. How do I refer to the the different workbook and range name in the formula? "Peo Sjoblom" wrote: You can download an example here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls adapt it to fit your needs -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Yes. All sheets are identical. "Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
=VLOOKUP(A2,INDIRECT("'[3DVLOOKUP.xls]"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'[3DVLOOKUP.xls]"&MySheets&"'!A2:A200"),A2)0),0))&"'!A2:C200"),2, 0)
-- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address "dford" wrote in message ... I still haven't quite got it yet. I have named a range with the worksheet names included called "Catagories" in a workbook called "Ingredients Spanish Fork". The Vlookup formula is in a different workbook. How do I refer to the the different workbook and range name in the formula? "Peo Sjoblom" wrote: You can download an example here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls adapt it to fit your needs -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Yes. All sheets are identical. "Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Hi again Peo, You are, of course, correct. I don't understand why these two formulae behave fundamentally differently (hardcoded array versus entering array within the spreadsheet cells), with the first returning a value and the second returning an array. Also, when I examine the outer "Index" array within your formula (by selecting cells over columns and rows and entering with ctrl + shift & enter, I can as expected see the entire array (all sheets) for the spreadheet cells option, but surprisingly only the sheet1 array for the hard-coded option: Spreadsheet cells option: =INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200") Hardcoded option: =INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"S heet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH( 1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A2)0),0))&"'!A2:C200") Do you know why the different behaviour applies? Peo Sjoblom Wrote: Hi John, the hardcoded formula works but if you use =VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)0) ,0))&"'!A2:C200"),2,0) you need to array enter it. -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531235 |
VLOOKUP on multiple sheets
I still can't seem to make this work. Below is the formula that works with
one sheet named "Meats". It is in a workbook called "Ingredients Spanish Fork". I have a range named in this workbook called "Catagories" of which Meats is one cell in the Catagory range. I need the formula to look at the "Catagories" range so it will include all 11 sheets in the range. =IF(ISERROR(VLOOKUP(A10,'[Ingredients Spanish Fork.xls]Meats'!$A$1:$E$1000,5,FALSE)),0,VLOOKUP(A10,'[Ingredients Spanish Fork.xls]Meats'!$A$1:$E$1000,5,FALSE)) "Peo Sjoblom" wrote: =VLOOKUP(A2,INDIRECT("'[3DVLOOKUP.xls]"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'[3DVLOOKUP.xls]"&MySheets&"'!A2:A200"),A2)0),0))&"'!A2:C200"),2, 0) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address "dford" wrote in message ... I still haven't quite got it yet. I have named a range with the worksheet names included called "Catagories" in a workbook called "Ingredients Spanish Fork". The Vlookup formula is in a different workbook. How do I refer to the the different workbook and range name in the formula? "Peo Sjoblom" wrote: You can download an example here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls adapt it to fit your needs -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Yes. All sheets are identical. "Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? |
VLOOKUP on multiple sheets
Hi dford, It seems you're wanting to not just do a Vlookup on multiple sheets, but do it in an entirely different workbook. This introduced other complications. Could I suggest simplifying your problem by first focusing on getting it working within a single workbook? Then you might consider adjustments to cope with any errors in formulas. Then when these problems are solved, you might consider adjustments to deal with your desire for links across different workbooks. Cheers, -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531235 |
VLOOKUP on multiple sheets
It works as =IF(ISERROR(VLOOKUP(A10,[Spanish.xls]Meats!$A$1:$E$1000,5,FALSE)),0,VLOOKUP(A10,[Spanish.xls]Meats!$A$1:$E$1000,5,FALSE)) which you can adapt to your longer names. ie, as =IF(ISERROR(VLOOKUP(A10,'[Ingrediants Spanish Pork.xls]Meats'!$A$1:$E$1000,5,FALSE)),0,VLOOKUP(A10,'[Ingrediants Spanish Pork.xls]Meats'!$A$1:$E$1000,5,FALSE)) -- QUOTE=dford]I still can't seem to make this work. Below is the formula that works with one sheet named "Meats". It is in a workbook called "Ingredients Spanish Fork". I have a range named in this workbook called "Catagories" of which Meats is one cell in the Catagory range. I need the formula to look at the "Catagories" range so it will include all 11 sheets in the range. =IF(ISERROR(VLOOKUP(A10,'[Ingredients Spanish Fork.xls]Meats'!$A$1:$E$1000,5,FALSE)),0,VLOOKUP(A10,'[Ingredients Spanish Fork.xls]Meats'!$A$1:$E$1000,5,FALSE)) "Peo Sjoblom" wrote: =VLOOKUP(A2,INDIRECT("'[3DVLOOKUP.xls]"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'[3DVLOOKUP.xls]"&MySheets&"'!A2:A200"),A2)0),0))&"'!A2:C200"),2, 0) -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address "dford" wrote in message ... I still haven't quite got it yet. I have named a range with the worksheet names included called "Catagories" in a workbook called "Ingredients Spanish Fork". The Vlookup formula is in a different workbook. How do I refer to the the different workbook and range name in the formula? "Peo Sjoblom" wrote: You can download an example here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls adapt it to fit your needs -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... Yes. All sheets are identical. "Peo Sjoblom" wrote: Are your worksheets identical in layout like table construction where they all are using $B$1:$E$1000 ? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "dford" wrote in message ... I use the formula below to search 1 worksheet. What is the best way to be able to search multiple worksheets? =IF(ISERROR(VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Ingredients Spanish Fork.xls]Sheet1'!$B$1:$E$1000,4,FALSE)) "Peo Sjoblom" wrote: On its way Peo "L. Howard Kittle" wrote in message ... Hi Peo, WOW....!! Could you send me an example workbook that demonstrates that lookup formula, please? Maybe with some description of some of the details..? Many thanks, as always, for your contributions. Regards, Howard "dford" wrote in message ... Can VLOOKUP be used to search columns in multiple sheets in a workbook? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531235 |
| All times are GMT +1. The time now is 05:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com