|
|
The Excel MATCH Function
The Excel MATCH function finds the relative position of a value in a supplied array. The user can request either an exact match or can request the position of the closest match (above or below), if an exact match is not found. The syntax of the function is :
MATCH( lookup_value, lookup_array, [match_type] )
where the arguments are as follows:
The Match function can be used to match numeric values, logical values, or text strings. Note that, when used with text strings, the function is NOT case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be a match. It is also useful to know the wildcards that can be used with text strings, when the [match_type] argument is set to 0 (requiring an exact match). These are:
These are shown in the examples below. Excel Match Function Example 1The following spreadsheet shows the Excel MATCH function used with the [match_type] argument set to 0. Therefore, in these examples, the function only returns a result if an exact match to the lookup_value is found. Otherwise, the function returns an error. The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results.
Note that, in the above examples, as the [match_type] argument is set to 0, the text strings in the lookup_array (cells A1-A5) do not need to be ordered. Match Function Example 2The following spreadsheet also shows the Excel MATCH function used with the [match_type] argument set to 0, but in this case the function is used to look up numeric values.
As in the previous examples, as the [match_type] argument is set to 0, the values in the lookup_array (cells A1-A6) do not need to be ordered. Match Function Example 3The following spreadsheet shows the Excel MATCH function used with the [match_type] argument set to 1. Therefore, in this example, the data in the lookup_array must be in ascending order, and the function returns the position of the exact match to the lookup_value if this is found, or the position of the closest value below the lookup_value if an exact match is not found.
Note that the [match_type] argument could have been omitted from the functions in this spreadsheet, as this argument takes the value 1 by default. Further information and examples of the Excel Match Function can be found on the Microsoft Office website. Match Function ErrorsIf you get an error from the Excel Match Function, this is likely to be one of the following: Common Errors
You may also experience the following common problem with the Excel Match function: Common Match Function Problem
The Excel Match function returns the wrong result Possible Reason
If your Excel Match function simply returns the wrong result, this is likely due to the lookup_array not being ordered correctly. Check the following:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Copyright © 2008-2011 ExcelFunctions.net |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
