MATCH function in excel is a lookup function. It helps in locating cell number corresponding to a lookup value of a row, column or table. It does this by searching for specific value in a range of cells and returns position of value. For Example, if the range A3:A6 have the values 10, 15, 20 respectively then function =MATCH(20,A3:A6,0) gives value 3 as 20 is 3rd in the range. It is also used commonly with the INDEX function.
=MATCH(Lookup_Value, Lookup_array, (Match_Type))
- Lookup_Value:- Here you will put the value which you want to match in Lookup_array. For example, when you are searching a name in your contact list, it is the lookup_value, but actually you want to find the mobile number.
- Lookup_array:- It is the range of cells or array reference in which lookup_value is being searched.
- Match_Type:- It is used to specify how you want to match lookup_value with the lookup_array values. By default its value is 1. But it can have 0,1,-1 values.
|1 or omitted||Find value less than (largest) or equal to Lookup_value.|
|0||Find value exactly equal to Lookup_value.|
|-1||Find value greater than (smallest) or equal to Lookup_value|
- MATCH is not case sensitive Or it do not differentiate between upper and lower case letters.
- If no value is found it returns #N/A error.
- A descending order is used for the lookup_array argument like 4,3,2,1,0.
- If lookup_value is in text form and match_type used is 0, than wildcards (*,?) can be used in lookup value.
- It is commonly used with INDEX function to find a value instead of position as a alternate to vlookup.
This is an example of Match_Type value ‘0’, Here the MATCH function searches for exactly same value as written in Lookup_value.
This is an example of Match_Type value ‘1’, here the function searches for the largest value which is smaller than or equal to the lookup_value.