In this tutorial I will tell you about the functionality and usage of “Match” function. A Match function is used to find out a specific string of text in a list of an array.
The functionality of match function is that it goes through each item in a range of array to find out a particular text and return its location or position.
Syntax of Match Formula:
In excel 2013, following is the syntax for Match formula:
=Match(Lookup_value, lookup_array, [match_type])
As per the figure Match-Function-in-Excel-1, Match function syntax consists of the following arguments:
Lookup_Value: The value that you want to match in the lookup_array aurgument.
Lookup_array: The range of cells that contains the value you are searching for.
[Match_type]: This is an optional Argument. It mentions that how Excel will match the value in the range of array that is being searched. There can be three values used in this argument. 1, 0, -1. The default is 1.
Following is the detail about the values in this argument.
If the value is 1, this means that the function will find a value which is less or equal to the lookup value. The range must be sorted in ascending order.
If the value is 0, this means that the function will find the first value that is equal to the lookup value.
If the value is -1, this means that the function will find the smallest value that is greater than and equal to the lookup value. The range must be sorted in descending order.
If we want to find out the position of list of items to use the match function.
Here is the data:
We use the following formula to calculate the appropriate position of the value:
Cell A2:A8 is the list of items where you want to apply the function.
Cell D3 consists of the item value you want to search.
Cell E3 consists of the return position based on the match function.
For the Match_type value to be 1, make sure to sort the list in ascending order.
For the Match_type value to be -1, make sure to sort the list in descending order.
Important notes about the “Match” Formula to keep in mind:
Match function is not case sensitive.
The argument “match_type” default value is 1.
If no match is found, the result is #N/A.
The match function returns a specific position of the string not the whole string.
Wild card operators can also be used in the lookup_value. Wild card consist of “?” and “*”.