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)
Search JabSto ::




Custom Search