Two-way Lookups


All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses Vlookup() to determine the tax rate for a given income amount.

The lookup functions in Excel and 1-2-3 are only appropriate for one-way lookups, however. If you need to perform a two-way lookup, you'll need more than the standard functions. FIGURE 2 shows a simple example

FIGURE 2: The formula in H4 performs a two-way lookup to determine the sales for a particular month and product.
The formula in cell H4 looks up the entries in cells H2 and H3 and then returns the corresponding value from the table. The formula in H4 is =index(A1:E14, match(H2,A1:A14,0), match(H3,A1:E1,0)).

The formula uses the Index() function, with three arguments. The first is the entire table range (A1:A14). The second uses Match() to return the offset of the desired month in column A. The third argument uses Match() to return the offset of the desired product in row 1.

Excel users can also take advantage of natural-language formulas. For example, enter =June Sprockets to return Sprocket sales for June. If natural-language formulas aren't working, select Tools*Options, click the Calculation tab, and place a check mark next to Accept labels in formulas.

-- John Walkenbach


Category:Word processing
Issue: February 2000

These Web pages are produced by Australian PC World © 1999 IDG Communications