Chapter 14: Creating Formulas That Look Up Values
325
Tip
Another way to accomplish a two-way lookup is to provide a name for each row and column of the table. A
quick way to do so is to select the table and choose Formulas ➪ Defined Names ➪ Create from Selection. In
the Create Names from Selection dialog box, select the Top Row and Left Column check boxes. After creating
the names, you can use a simple formula, such as:
= Sprockets July
This formula, which uses the range intersection operator (a space), returns July sales for Sprockets. See
Chapter 10 for details about the range intersection operator. n
Performing a two-column lookup ............................................................................
Some situations may require a lookup based on the values in two columns. Figure 14.13 shows an
example.
FIGURE 14.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