Perform Two-Way Table Lookups

Category: Formulas | [Item URL]

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 the VLOOKUP function to determine the tax rate for a given income amount.

The lookup functions in Excel 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. The figure below shows a simple example.

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 the MATCH function to return the offset of the desired month in column A. The third argument uses the MATCH function to return the offset of the desired product in row 1.

You may prefer to take advantage of Excel's natural-language formulas. For example, enter the following formula to return Sprocket sales for June:

=June Sprockets 

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." Be aware that using natural language formulas is not 100% reliable!