Microsoft Office Tutorials and References
In Depth Information
Using implicit intersection
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 combine these formulas into a single formula, as shown here:
=INDEX(Table,MATCH(Month,MonthList,0),MATCH(Product,ProductList,0))
Using implicit intersection
The second method to accomplish a two-way lookup is quite a bit simpler, but it requires that
you create a name for each row and column in the table.
A quick way to name each row and column is to select the table and choose Formulas
Defined
Names
Create From Selection. In the Create Names From Selection dialog box, specify that the
names are in the top row and left column. Click OK, and Excel creates the names. 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 data for
Sprockets.
 
Search JabSto ::




Custom Search