Microsoft Office Tutorials and References
In Depth Information
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)
The final formula, in cell B6, returns the corresponding sales amount. It uses the INDEX function with the res-
ults from cells B4 and B5.
=INDEX(Table,B4,B5)
You can 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 choose Formu-
las 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.
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.
Search JabSto ::




Custom Search