Microsoft Office Tutorials and References
In Depth Information
Figure 8-13: This workbook performs a lookup by using information in two columns (D and E).
The lookup table contains automobile makes and models, and a corresponding code for each. The worksheet
uses named ranges, as shown here:
F2:F12 Code
B1
Make
B2
Model
D2:D12 Makes
E2:E12 Models
The following array formula displays the corresponding code for an automobile make and model:
{=INDEX(Code,MATCH(Make&Model,Makes&Models,0))}
This formula works by concatenating the contents of Make and Model and then searching for this text in an ar-
ray consisting of the concatenated corresponding text in Makes and Models .
Determining the address of a value within a range
Most of the time, you want your lookup formula to return a value. You may, however, need to determine the cell
address of a particular value within a range. For example, Figure 8-14 shows a worksheet with a range of num-
bers that occupy a single column (named Data ). Cell B1, which contains the value to look up, is named Target.
Search JabSto ::




Custom Search