Excel 2010 Bible

Part II: Working with Formulas and Functions

324

FIGURE 14.12

This table demonstrates a two-way lookup.

To simplify things, the worksheet uses the following named ranges:

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)

The final formula, in cell B6, returns the corresponding sales amount. It uses the INDEX function

with the results from cells B4 and B5.

=INDEX(Table,B4,B5)

You can, of course, combine these formulas into a single formula, as shown here:

=INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0))