Lookup Data Across Multiple Worksheets

[Lee4]

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?

 

[Dave]

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.

 

[Lee4]

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.

 

[Peo Sjoblom]

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/advanced_function_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

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.

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.

 

[Lee4]

Peo,

Thank you. That is much more efficient and elegant than the other
workaround that I located.

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/advanced_function_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

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.

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.