Microsoft Office Tutorials and References

In Depth Information

**Tip 35: Performing a Two-Way Lookup**

Performing a Two-Way Lookup

A two-way lookup identifies the value at the intersection of a column and a row. This tip describes

two methods to perform a two-way lookup.

Using a formula

Figure 35-1 shows a worksheet with a range that displays product sales by month. To retrieve sales

for a particular month and product, the user enters a month in cell B1 and a product name in cell B2.

Figure 35-1:
This table demonstrates a two-way lookup.

To simplify the process, the worksheet uses the named ranges shown in the following minitable.

Name

Refers To

Month

B1

Product

B2

Table

D1:H14

MonthList

D1:D14

ProductList

D1:H1

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)