Lookup across Worksheets
Posted by Greg on March 08, 2001 10:54 AM
Assume I have a workbook that consists of 6 different sheets, one called Summary and the others called Property 1, Property 2, Property 3....; How do I write a lookup formula that will search for the specific sheet and then for a value on that sheet? I know how to do a simple lookup for a value for Property 1, but how about a more robust equation that could look up information on all of them?
Posted by Aladin Akyurek on March 09, 2001 4:11 PM
Maybe
Just a tought. Unsure about the payoff though.
You can create a lookup table of lookup tables. I'll give an example.
I have 3 sheets. I want pull off a value which can be found in one of the tables on different sheets.
On sheet 2 I have a 2-column lookup table:
2 a
3 c
4 d
I named this range Table1 via the Name Box.
On Sheet 3 I have another 2-column lookup table:
5 p
6 q
7 r
I named this range Table 2 via the Name Box.
Now I create a lookup table of all my lookup tables on Sheet 1:
2 4 Table1
5 7 Table1
I name this range TOT.
Suppose I have a lookup value in A1 on Sheet1 and I want to find the value associated with:
In B1 I enter:
=IF(AND(A1>=VLOOKUP(A1,TOT,1),A1<=VLOOKUP(A1,TOT,2)),VLOOKUP(A1,INDIRECT(VLOOKUP(A1,TOT,3)),2),"")
This formula computes "a" as the value associated with "2".
Aladin
