Page 330 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 287
Creating Formulas That Look Up Values
FIGURE 15.13
This workbook performs a lookup by using information in two columns (D and E).
sheet uses named ranges, as shown here:
Code
F2:F12 The lookup table contains automobile makes and models and a corresponding code for each. The work- 15
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
array consisting of the concatenated corresponding text in Makes and Models.

Determining the cell 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 15.14 shows a worksheet with a range
of numbers that occupies a single column (named Data). Cell B1, which contains the value to look up, is
named Target.

287