Microsoft Office Tutorials and References
In Depth Information
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 121-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 121-1: This table demonstrates a two-way lookup.
To simplify the process, the worksheet uses the following named ranges:
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)
 
Search JabSto ::




Custom Search