Use Worksheet Functions to Perform 2-D Table Lookups

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Use Worksheet Functions to Perform 2-D Table Lookups

by Sean Kavanagh

Application: Microsoft Excel 2000
Operating System: Microsoft Windows

If you frequently use worksheet functions, chances are you've used VLOOKUP or HLOOKUP before. If you aren't familiar with them, these functions perform table lookups. To use a formula to execute a table lookup, you supply a known value you want to find to the appropriate lookup function. Then, the function searches for that value in a table range. If a match is found, the function returns a corresponding value that's a specific number of columns or rows away from the found data.

The VLOOKUP and HLOOKUP functions are powerful, but they have limitations. One of which is that you often need to search a table based on more than just one value. For example, imagine you have a price list set up like the sample data shown in Figure A. Prices are based upon multiple dimensions, in this case height and width. If you want to use a formula to determine prices based upon varying specifications, you need more flexibility than the VLOOKUP or HLOOKUP functions can provide.

Figure A: A VLOOKUP formula returns the appropriate price for a set height of 0.25, but the function isn't flexible enough to handle varying heights.

In this article, we'll show you how to create a formula that's capable of performing lookups based on two variables. We'll first look at a traditional VLOOKUP formula to examine its limitation. Then, we'll show you how to overcome the problem using the INDEX and MATCH functions. Finally, we'll look at using an optional function argument to deal with cases where users specify criteria values that don't exactly match an item in your lookup table.

Illustrating the VLOOKUP limitation

For an example of when you need a more powerful function than just VLOOKUP, we'll use the example data shown in Figure A. If we assume we're looking for a particular height, say 0.25, we can use the VLOOKUP formula in cell B13 to determine the price based on width:

=VLOOKUP(B10,B3:H8,2)

The function looks for the width value stored in B10 in the first column of the range B3:H8. If a match is found, the formula returns the corresponding price from the second column of the B3:H8 search.

The problem with this formula is that it won't work if you want to look up a price for a different height. To make it work, you can manually change the number that represents the column you want to return data from, in this case, 2. An alternative is to come up with a way to make the third function argument dynamic so that it determines the column offset based on the height you specify.

Using the MATCH function, you can make the third argument dynamic. However, you can actually replace VLOOKUP altogether and use a combination of INDEX and MATCH to get the same results. These lookup functions tend to be lesser known, but they're extremely useful. This is especially true when they're used together, so it's worth taking a look at a solution that uses them exclusively.

Before we examine the INDEX and MATCH functions, you may want to set up some sample data so that you can follow along with our example. You can download the workbook from the FTP site listed at the beginning of this article or set up the data yourself. If you manually set it up, don't worry about formatting it exactly as we did or using the same values—as long as your data is structured similarly, you can follow the basic steps.

The INDEX function

The INDEX function returns a value from a given range based on a row and column position number. Its simplest form is

INDEX(array,row_num,column_num)

The row_num value corresponds to the row a particular cell is in, in relation to the range defined by the array argument. Likewise, the column_num argument is the cell's corresponding column number within the range. Therefore, the upper-left cell in a range has a row_num of 1 and column_num of 1. The cell to the right of it would have a row_num of 1 and column_num of 2, and so on.

To better illustrate the way the function works, let's create a basic INDEX formula. First, we'll set up a named range to make it easier to work with the function. To do so, select range C3:H8. Then, choose Insert | Name | Define from the menu bar. Enter the name Prices in the Names In Workbook text box and click OK. Now, in cell D10, enter the formula

=INDEX(Prices,2,2)

and Excel returns the value 5.16, the value that's stored in the second row and second column of the Prices range.

You can see how easy it is to return a value based on two-dimensional criteria with INDEX. The challenge is how to find a way to use row_num and column_num arguments based on the width and height values specified in cells B10 and B11. That's where the MATCH function comes in.

The MATCH function

The MATCH function gives you the relative position on a particular value within a single-row or single-column range. The general form of this function is

MATCH(lookup_value,lookup_array)

The MATCH function lets us determine both the row and column numbers we need to supply to the INDEX function. To illustrate MATCH, let's create a formula that gets the required row number, which corresponds to our width variable. First, use the previously described steps to create a named range called Width that applies to the range B3:B8.

Next, in cell E10, enter the formula:

=MATCH(B10,Width)

The first argument of the formula tells the function to look for the value stored in cell B10. The second argument tells the function to look for the value in the range named Width. The function returns the value 4 because the cell containing the value in B10, 1.00, is the fourth cell in the Width range B3:B8.

Similarly, define a named range called Height for the range C2:H2. Then, select cell F10 and enter the formula:

=MATCH(B11, Height)

The function returns the value 1, since the cell containing 0.25 is the first in the Height range.

Creating a two-dimensional lookup

To illustrate how everything comes together, let's replace our previously used VLOOKUP formula. Select cell B13 and enter the formula:

=INDEX(Prices, MATCH(B10, Width),
    MATCH(B11, Height))

The MATCH functions find the appropriate row and column numbers and the INDEX function provides the price based on those positions. Try plugging in some different values in the B10 and B11 cells. As you can see in Figure B, the formula compensates when you change either parameter.

Figure B: Combining INDEX and MATCH functions lets us perform two-dimensional lookups.

The optional match_type argument

The MATCH function actually has a third, optional argument, match_type. When you omit this argument, if MATCH can't find the exact value specified by the lookup_value argument, it returns the number for the last value that's less than the value you're looking for.

To see why this is important to know, change the Height parameter in cell B11 to 0.80. The formula continues to return the price for a height of 0.75. Consumers buying irregular sized products might appreciate a pricing scheme like this, but as a business owner you'd probably prefer the price to slide in the opposite direction. Fortunately, you can use the match_type argument to exert control over how MATCH handles imprecise lookup matches.

Table A shows the accepted match_type values. If you omit the argument, the default value is 1. Using the match_type argument, you can change our formula to return only exact matches with a MATCH function such as:

MATCH(B11, Height, 0)

If a user enters an invalid height value, the function returns the error #N/A. Of course, you'd also want to change the MATCH function that calculates the Width index number.

Table A: Match_type argument values

Note that the order of the items within lookup_array is critical for proper MATCH function results. For instance, if you want to round up to the next highest price when an irregular size is entered, the price list must be organized as shown in Figure C. The prices for both height and width are listed in descending order. You must also specify the -1 match_type value for both of the MATCH functions to produce the result shown in our example.

Figure C: We're able to round up to the next highest price when irregular sizes are entered by re-sorting our table and adding a match_type argument of -1.

Two functions are better than one

In some instances, a VLOOKUP or HLOOKUP function isn't enough to meet your lookup needs. When you need to perform table lookups based on two variables, consider using INDEX and MATCH in conjunction. As we've shown you, the MATCH function not only provides the required row and column positions that the INDEX function needs to return results, but it can also be used to control how near-matches are handled when inexact values are passed to your formula.

© 2001 Element K Journals, a division of Element K Press LLC ("Element K"). All rights reserved except for the right to view this site using a web browser and to make private, noncommercial use hereof. Element K and the Element K logo are trademarks of Element K LLC. The content published on this site ("Content") is the property of Element K or its affiliates or third party licensors and is protected by copyright law in the U.S. and elsewhere. This means that the right to copy and publish the Content is reserved, even for Content such as tips and articles made available for free, none of which may be copied in whole or in part or further distributed in any form or medium without the express written permission of Element K. Questions or requests for permission to copy or republish any content may be directed to: contentreuse@elementk.com.