MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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:


This formula computes "a" as the value associated with "2".