Excel 2010 Bible

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