How to Perform a Two-Dimensional Lookup
The information in this article applies to:
  • Microsoft Excel 2000

SUMMARY

You can use Microsoft Excel 2000 to produce two-dimensional lookups in a worksheet. A two-dimensional lookup is a method of finding data with two arguments. The two arguments in an Excel worksheet are typically a row and a column. This article describes two methods to perform a two-dimensional lookup; one method uses the INDEX and MATCH worksheet functions and another method uses natural language formulas.


MORE INFORMATION

 

Method 1: INDEX and MATCH Worksheet Functions

In this example, the INDEX worksheet function returns a value (the support number for a specific product) in the array A2:C4. The MATCH worksheet function determines the row that contains the value that you want, and then you identify the column that you want as column 3.

To use the INDEX and MATCH worksheet functions in Excel to perform two-dimensional lookups, follow these steps:
  1. Start Excel, and then create the following spreadsheet:

       A1: Product Code   B1: Product Name       C1:  Support Number
   A2: 222            B2: Microsoft Excel    C2:  111-222-3333
   A3: 111            B3: Microsoft Word     C3:  333-444-5555
   A4: 333            B4: Microsoft Office   C4:  222-333-4444

   E1: Code           F1: ID#                G1: Name
   E2: 111            F2: Microsoft Word
  2. Type the following formula in cell G2:

    =INDEX(A2:C4,MATCH(E2&F2,A2:A4&B2:B4,0),3)
  3. Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

  4. The formula returns 333-444-5555.

Method 2: Natural Language Formulas

A natural language formula is a method of intuitively entering a formula to look up a value. For example, if a column heading is labeled 2000, and rows are labeled by month, a natural language formula for finding a value for Jan 2000 is:
= Jan 2000
The natural language formula works as expected whether it is expressed in a row-column or column-row format. For example, the following formula
=2000 Jan
returns the same value as =Jan 2000.

To use a natural language formula in Excel to perform two-dimensional lookups, follow the steps in the following examples.

Example 1

  1. Start Excel, and then create the following spreadsheet:

       A1:        B1: 1999    C1:2000
   A2: Jan    B2: 100     C2: 40
   A3: Feb    B3: 110     C3: 55
   A4: Mar    B4: 105     C4: 60
  2. On the Tools menu, click Options.

  3. On the Calculation tab, ensure that the Accept labels in formulas check box is selected, and then click OK.

  4. In cell D1, type the following formula:

    =2000 Feb
    The formula returns the value 55.

Example 2

Tables may contain more than one column or row label heading. You can create natural language formulas that refer to multiple headings. To do so, type a space between each of the labels in the formula. The following example uses a stacked column label in a formula:
  1. Start Excel, and then create the following spreadsheet:

       A1:        B1: 1999   C1:         D1: 2000    E1:
   A2:        B2: North  C2: South   D2: North   E2: South
   A3: Jan    B3: 100    C3: 50      D3: 200     E3: 70
   A4: Feb    B4: 105    C4: 60      D4: 205     E4: 80
   A5: Mar    B5: 110    C5: 70      D5: 210     E5: 90
  2. For clarity, you can merge cells B1:C1 and cells D1:E1, and then center-align the headings. To merge cells and then center-align the text, follow these steps:

    • Select cells B1 and C1.

    • On the Format menu, click Cells.

    • Click the Alignment tab.

    • Under Text control, click to select the Merge cells check box.

    • Under Text alignment, in the Horizontal box, click Center, and then click OK.

  3. In cell G1 type the following formula:

    =1999 North Feb
    The formula returns the value 105.

 


REFERENCES

For additional information about natural language formulas, click the article number below to view the article in the Microsoft Knowledge Base:

Q211522 XL2000: Limit of Natural Language Formulas per Worksheet
For more information about the INDEX worksheet function, click Microsoft Excel 2000 Help on the Help menu, type index in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the MATCH worksheet function, click Microsoft Excel 2000 Help on the Help menu, type match worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

 

Additional query words: XL2000

Keywords :
Issue type : kbhowto
Technology : kbExcelSearch kbPictureIt2000

 

Last Reviewed: October 22, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.

 

 

Created Date: 10/17/2001  Last Reviewed: 10/17/2001  Rev. Date: