XL: Creating a Two-Dimensional Lookup Table in Microsoft Excel

ID: Q76428

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for Windows 95, version 7.0

SUMMARY

A two-dimensional lookup table is one in which the desired value depends on two different variables. The following formula finds a value in a table, based on two lookup values:


   =INDEX(Data_Table,MATCH(Row_Val,Row_Headers),MATCH(Col_Val,Col_Headers))

This formula works well in cases where using the Intersection operator (space) will not work (for example, when lookup values are numbers or do not match row and/or column headings exactly).

MORE INFORMATION


   This variable  Should be substituted with this range or value
   -------------------------------------------------------------

   Data_Table     The range containing the entire source table,
                  including column and row headers.

   Row_Val        The value to look up in the row headers.

   Row_Headers    The row headers; this should be the left column of
                  Data_Table.

   Col_Val        The value to look up in the column headers.

   Col_Headers    The column headers; this should be the top row of
                  Data_Table.

NOTE: Both column and row headers must be sorted in ascending order to use this formula. Note also that either the column or row headers can specify a range of values, rather than a single value. However, to specify a range, the header must use the minimum number in that column (for example, the header for a column that specifies the range 137-142 should be 137).

Example

The following is a two-dimensional table that displays the cost per unit of several products. The cost per unit varies, depending on the number of items ordered. Column A contains the Product Name. Row 1 contains the minimum number of items that must be purchased to obtain the purchase price in that particular column:


   A1:             B1: 1           C1: 10          D1: 100
   A2: MS Excel    B2: $395        C2: $350        D2: $300
   A3: Mouse       B3: $99         C3: $89         D3: $79
   A4: MS Project  B4: $695        C4: $610        D4: $500
   A5: Word        B5: $495        C5: $425        D5: $350

Assume you want to find the unit price if you purchase 15 units of MS Project. If you enter "MS Project" (without the quotation marks) into cell A7 and the number 15 in cell A8, you can return the unit price by entering the following formula:


   =INDEX(A1:D5,MATCH(A7,A1:A5),MATCH(A8,A1:D1))

This formula returns $610.

Example 2

To use the values in A8 and B8 to indicate the two values to be cross-referenced on the table


    A1:     B1:  1   C1:  2    D1:  3   E1:  4   F1:  5
    A2: 1   B2:  1   C2:  2    D2:  3   E2:  4   F2:  5
    A3: 2   B3:  2   C3:  4    D3:  6   E3:  8   F3: 10
    A4: 3   B4:  3   C4:  6    D4:  9   E4: 12   F4: 15
    A5: 4   B5:  4   C5:  8    D5: 12   E5: 16   F5: 20
    A6: 5   B6:  5   C6: 10    D6: 15   E6: 20   F6: 25
    A7:     B7:      C7:       D7:      E7:      F7:
    A8: 4   B8:  5   C8:       D8:      E8:      F8:

use the following formula:


   =INDEX(B2:F6,MATCH(A8,B1:F1,0),MATCH(B8,A2:A6,0))

The result of this formula is 20.

If you want to look for the largest value less than or equal to the values searched for (useful for a commission table, tax table, and so on), change the zeros in the MATCH functions to ones.

REFERENCES

For more information about the MATCH() and INDEX() functions, click the Index tab in Microsoft Excel version 7.0 Help, type the following text


   lookup functions

click Display, and then double-click the "Lookup & Reference Functions" topic to go to that topic.

"Function Reference," version 4.0, pages 236-237, 267
"Microsoft Excel Function Reference," version 3.0, pages 127-128, 148

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 Cross-Reference multiplication table commission sheet

Keywords :
Version :
Platform :
Issue type :