Microsoft KB Archive/76428

{|
 * width="100%"|

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

 * }

-

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, 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 &quot;MS Project&quot; (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.