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.
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):
The formula in cell B5 works similarly but uses the ProductList range: