Microsoft KB Archive/324989

= XL2002: How to Use the MATCH Function to Find Data in a Table =

PSS ID Number: 324989

Article Last Modified on 4/28/2003

-

The information in this article applies to:


 * Microsoft Excel 2002

-



This article was previously published under Q324989



SUMMARY
This article describes how to use the MATCH function to find the relative position of an item in a range, if a match is found.



MORE INFORMATION
Enter the following data into a blank Excel worksheet. You will use this data for all sample formulas in this article.

Enter the following formula into cell E2 (or any available blank cell):

=MATCH(A7,C2:C5,0)


 * A7 - The value that you want to find.
 * C2:C5 - The lookup range where the value that you want will be found.
 * 0 - Indicates whether you are looking for a value in C2:C5 that matches the value in A7 exactly (use &quot;0&quot;), close but less than the value in A7 (use &quot;1&quot;), or close but greater than the value in A7 (use &quot;-1&quot;).

Examples
The following examples show how to use &quot;0&quot; for an exact match to the value in cell A7, &quot;1&quot; for the largest match that is less than the value in cell A7, and &quot;-1&quot; for the smallest match that is greater than the value in cell A7. Next to each formula is the relative position that the formula will return.
 * =MATCH(A7,C2:C5,0) = 1

In this formula, the MATCH function returns the relative position of the value in the specific lookup range (C2:C5) if a value is found that is an exact match of the value in cell A7 found. An exact match for the value in A7 (28) is found in cell C2. C2 is the first item in the lookup range (C2:C5). Therefore, its relative position is 1.
 * =MATCH(A7,C2:C5,1) = 3

In this formula, the MATCH function returns the relative position of the value in the specified lookup range (C2:C5) if a value is found that is less than the value in cell A7. The largest number in column C that is less than 28 is 22 (found in C4). C4 is the third cell in the lookup range (C2:C5). Therefore, its relative position is 3.
 * =MATCH(A7,C2:C5,-1) = 4

In this formula, the MATCH function returns the relative position of the value in the specified lookup range (C2:C5) if a value is found that is greater than the value in cell A7. The smallest number in column C that is greater than 28 is 29 (found in C5). C5 is the fourth cell in the lookup range (C2:C5). Therefore, its relative position is 4.

Note Use MATCH instead of one of the LOOKUP functions when you want to find the position of an item in a range instead of the item itself.

Additional query words: Additional query words:

inf XL2002 XLXP comparison locate coordinate

Keywords: kbhowto KB324989

Technology: kbExcel2002 kbExcel2002Search kbExcelSearch kbExcelWinSearch

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2003 Microsoft Corporation. All rights reserved.