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!

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2019, J-Walk & Associates, Inc.
Privacy Policy