Lookup Data Across Multiple Worksheets
I need the ability to look data across multiple worksheets. In essence, I
need something like VLookup or HLookup, but I need a 2-dimensional search across (a) a given row or column on one worksheet and (b) the same row/column in each worksheet in a defined group --, rather than 2-dimensional across a row and column, as in VLookup and HLookup. Any ideas? |
Lookup Data Across Multiple Worksheets
Hi,
I don't think VLOOKUP does the 3-dimensional thing. You may need to create a summary sheet, and send your VLOOKUP there for its array. Regards - Dave. |
Lookup Data Across Multiple Worksheets
Dave:
Thanks, and I know that VLookup and HLookup will not work in 3-D, but I'm not looking for a 3-D search. I'm looking for a 2-D search, but not on the X-Y plane (row by column) -- instead, a 2-D search on the Y-Z plane (single row or column by multiple worksheets). As an aside, I finally found a post that will "work" using the IF() function, but it would be cumbersome with more than a [rather small] number of worksheets. My project would involve less than a dozen worksheet of "depth" (so it will work for me), but I would still like to know whether there is a function that would do this more efficiently. Thanks. "Dave" wrote: Hi, I don't think VLOOKUP does the 3-dimensional thing. You may need to create a summary sheet, and send your VLOOKUP there for its array. Regards - Dave. |
Lookup Data Across Multiple Worksheets
As soon as you are using more than one sheet it is considered 3D
There is a method albeit quite complex number 5 here http://www.nwexcelsolutions.com/adva...ction_page.htm download a sample here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls it takes that you create a list of all sheets involved -- Regards, Peo Sjoblom "Lee4" wrote in message ... Dave: Thanks, and I know that VLookup and HLookup will not work in 3-D, but I'm not looking for a 3-D search. I'm looking for a 2-D search, but not on the X-Y plane (row by column) -- instead, a 2-D search on the Y-Z plane (single row or column by multiple worksheets). As an aside, I finally found a post that will "work" using the IF() function, but it would be cumbersome with more than a [rather small] number of worksheets. My project would involve less than a dozen worksheet of "depth" (so it will work for me), but I would still like to know whether there is a function that would do this more efficiently. Thanks. "Dave" wrote: Hi, I don't think VLOOKUP does the 3-dimensional thing. You may need to create a summary sheet, and send your VLOOKUP there for its array. Regards - Dave. |
Lookup Data Across Multiple Worksheets
Peo,
Thank you. That is much more efficient and elegant than the other workaround that I located. "Peo Sjoblom" wrote: As soon as you are using more than one sheet it is considered 3D There is a method albeit quite complex number 5 here http://www.nwexcelsolutions.com/adva...ction_page.htm download a sample here http://nwexcelsolutions.com/Download/3DVLOOKUP.xls it takes that you create a list of all sheets involved -- Regards, Peo Sjoblom "Lee4" wrote in message ... Dave: Thanks, and I know that VLookup and HLookup will not work in 3-D, but I'm not looking for a 3-D search. I'm looking for a 2-D search, but not on the X-Y plane (row by column) -- instead, a 2-D search on the Y-Z plane (single row or column by multiple worksheets). As an aside, I finally found a post that will "work" using the IF() function, but it would be cumbersome with more than a [rather small] number of worksheets. My project would involve less than a dozen worksheet of "depth" (so it will work for me), but I would still like to know whether there is a function that would do this more efficiently. Thanks. "Dave" wrote: Hi, I don't think VLOOKUP does the 3-dimensional thing. You may need to create a summary sheet, and send your VLOOKUP there for its array. Regards - Dave. |
All times are GMT +1. The time now is 05:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com