Excel Match Function
Excel Match Function Example: The Excel Match function is one of the most powerful lookup tools of Excel. The function is one of the alternative syntax for vertical lookup. VLOOKUP function has numerous limitations, preventing it from attaining desired results.
Excel Match function is more superior because it is more flexible and has more features than VLOOKUP. The Excel Match function may be used with the Index function. This enables it to search for values that are table based. It can be used to search where there are rows, columns, or both simultaneously.
The Excel Match function searches for values in an array and returns its position in the array. A user may opt the function to return a result if precise match is found or return closest match if exact value cannot be found.
Excel Match Function Example Syntax
The syntax for Match function in excel is written as MATCH (value, array, [match_type]).
The value in the syntax represents the search value while the array is the range of cells that has the value being searched. The match_type represents the match that the function performs. The match type possible values include 1 or default which results in largest number, less than or equal to the value.
Arrays should therefore be sorted in ascending order. In cases of match_type omission, the match_type of one will be assumed. The next possible value is 0 which leads to the function finding the first value that is equal to the value. In this case the array may be sorted in whichever order. The next possible value is -1 whereby the function finds the smallest value, greater than or equal to the value. The array has to be sorted in descending order.
The Match Syntax is not case-sensitive during the search process. During search process the Match function may sometimes fail to find a match, in this case a #N/A error will be returned. In other cases when the match_type possible value is 0, wildcards may be used.
These wildcards include the *, which matches sequence of characters. The ? wildcard will match single characters. To find the * or ? character, you must type ~ before the character during your search. For example a condition with D*s will match every cell with text string that start with D and ends with s. The Match function may be used to match logical values, numeric values and text strings.
Errors in the Excel Match function
The most common error in the Excel Match function is the #N/A error. This error occurs whenever the function fails to find match for the searched value.
This occurs if in the match_type value was equal to 0, an exact match for the searched value was not found within the array. In another instance is the one where the match_type value is equal to 1 or omitted, the first value in the array is larger than the searched value. In this case if the array is in ascending order, then there will be no closest match below or equal to the searched value.
Then for match_type of -1, the first value in array is smaller than the searched value. In case the array order is descending, there is no nearest match above or equal to the searched value.