Microsoft KB Archive/275170

= HOW TO: Perform a Two-Dimensional Lookup in Excel 2000 =

Article ID: 275170

Article Last Modified on 10/8/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q275170



IN THIS TASK
SUMMARY Method 1: INDEX and MATCH Worksheet Functions Method 2: Natural Language Formulas
 * Example 1
 * Example 2

REFERENCES



SUMMARY
This step-by-step article shows you how to 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.

back to the top

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:   Start Excel, and then create the following worksheet:   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  Type the following formula in cell G2:

=INDEX(A2:C4,MATCH(E2&F2,A2:A4&B2:B4,0),3)

 Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns 333-444-5555.

back to the top

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.

back to the top

Example 1
  Start Excel, and then create the following worksheet: <pre class="fixed_text">  A1:        B1: 1999    C1:2000 A2: Jan   B2: 100     C2: 40 A3: Feb   B3: 110     C3: 55 A4: Mar   B4: 105     C4: 60 </li> On the Tools menu, click Options.</li> On the Calculation tab, make sure that the Accept labels in formulas check box is selected, and then click OK.</li> In cell D1, type the following formula:

=2000 Feb

The formula returns the value 55.</li></ol>

back to the top

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:   Start Excel, and then create the following worksheet: <pre class="fixed_text">  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 </li> 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: <ul> Select cells B1 and C1.</li> On the Format menu, click Cells.</li> Click the Alignment tab.</li> Under Text control, click to select the Merge cells check box.</li> Under Text alignment, click Center in the Horizontal box, and then click OK.</li></ul> </li> In cell G1, type the following formula:

=1999 North Feb

The formula returns the value 105.</li></ol>

back to the top

<div class="references_section">