How to Perform a TwoDimensional Lookup

The information in this article applies to:
SUMMARY
You can use Microsoft Excel 2000 to produce twodimensional lookups
in a worksheet. A twodimensional 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 twodimensional 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
twodimensional lookups, follow these steps:
 Start Excel, and then create the following spreadsheet:
A1: Product Code B1: Product Name C1: Support Number
A2: 222 B2: Microsoft Excel C2: 1112223333
A3: 111 B3: Microsoft Word C3: 3334445555
A4: 333 B4: Microsoft Office C4: 2223334444
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 3334445555.
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 rowcolumn or columnrow 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 twodimensional
lookups, follow the steps in the following examples.
Example 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
 On the Tools menu, click Options.
 On the Calculation tab, ensure that the Accept labels in
formulas check box is selected, and then click OK.
 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:
 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
 For clarity, you can merge cells B1:C1 and cells D1:E1, and then
centeralign the headings. To merge cells and then centeralign 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.
 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 