The information in this article applies to:
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.
Method 1: INDEX and MATCH Worksheet FunctionsIn 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:
Method 2: Natural Language FormulasA 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 2000The natural language formula works as expected whether it is expressed in a row-column or column-row format. For example, the following formula
=2000 Janreturns 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 2Tables 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:
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 WorksheetFor 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
Last Reviewed: October 22, 2000
Created Date: 10/17/2001 Last Reviewed: 10/17/2001 Rev. Date: