Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

The following formula (in cell B4) uses the MATCH function to return the position of the Month

within the
MonthList
range. For example, if the month is January, the formula returns 2 because

January is the second item in the
MonthList
range. (The first item is a blank cell, D1.)

=MATCH(Month,MonthList,0)

The formula in cell B5 works similarly but uses the
ProductList
range:

=MATCH(Product,ProductList,0)

The final formula, in cell B6, returns the corresponding sales amount. It uses the INDEX function

with the results from cells B4 and B5.

=INDEX(Table,B4,B5)

You can, of course, combine these formulas into a single formula, as shown here:

=INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0))

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 this is to select the table and use Formulas➜

Defined Names

Create from Selection. After creating the names, you can use a simple

formula to perform the two-way lookup, such as

➜

=Sprockets July

This formula, which uses the range intersection operator (a space), returns July sales

for Sprockets. To refer to the cells where the month and product are entered, use

=INDIRECT(Month) INDIRECT(Product)

This formula converts the values in the cells
Month
and
Product
into range references and

finds the intersection. See Chapter 3 for details about the range intersection operator.

Performing a two-column lookup

Some situations may require a lookup based on the values in two columns. Figure 8-13 shows an

example.