Match function is one the most important and widely used functions in Excel. In this post let us see how to use the Match Function in excel. Syntax of Match function and few examples on how to use the Match function in excel.
What is MATCH Function in Excel
Match Function in Excel is under the category of look up or reference functions. Match Function searches for a specific or selected item in a row or column and then returns the relative position of that item in that particular row or column.
Syntax of Match Function
The Syntax of Match function in excel is as follows.
MATCH(lookup_value,lookup_array,[Match Type])
Arguments of MATCH Function
Let us now understand the syntax of match function in detail. MATCH function has 3 arguments.
lookup_value : The value you want to look up for in an array. This can be a number, text, cell reference or a logical value. This is a required argument.
lookup_array : The range of cells that you want excel to search for the look up value. This is a required argument.
Match Type : The type of match which you want to have with look up value. This is an optional argument. Match Type argument supports 3 different type of matches as follows.
1 – Less Than – Using this option in Match Type the function returns the position of largest value that is less than or equal to the lookup value from the lookup array. In simple terms match returns the exact value or the value that is next smallest to look up value from the look up array. The array should be be sorted in ascending order.
0 – Exact Match – Using this option in Match Type the function returns the position of exact Match value to look up value. No need to sort the array.
-1 – Greater Than – Using this option in Match Type the function returns the position of smallest value that is greater than or equal to the lookup value from the lookup array. In simple terms match returns the exact value or the value that is next largest to look up value from the look up array. Make sure to sort the array in Descending order if not the function returns #N/A error.
In cases where match type is not specified. excel considers Match Type as 1 by default.
Examples of Match Function
See the below examples to understand the excel match function while choosing different match types.
Match Type 1
In the above image, we can see the Product list an respective cunt of items in range B3 to C9.
Now lets evaluate the formula Match(F3,C2:C9,1).
Here the look up value is in cell F3 which is 100. In the look up array which is C2:C9, the look up value 100 is not available. Hence the formula returns the position of next larger number in the array and less than the look up value which is 99. 99 is in the 4th row in the array (including header row). Therefore the return value is 4.
(Exact) Match Type 0
In the above image, we can see the employee list in range B3 to B12. We need to find out the position of the selected employee (cell E4) in the employee list.
The look up value here is in cell E4 which is “Lisa”. Lookup array is the range from B3 to B12. The match type is exact match (0).
Now Match looks for Lisa in the array B3:B12 . Lisa is in the 5th row in the array (including header row). Therefore the return value is 5.
Match Type -1
Let us see the same example which we used above for Match type 1. See the above image
Now lets evaluate the formula Match(F3,C2:C9,-1).
Here the look up value is in cell F3 which is 100. In the look up array which is C2:C9, the look up value 100 is not available. Hence the formula returns the position of immediate next larger number in the array and greater than the look up value which is 104. 104 is in the 5th row in the array (including header row). Therefore the return value is 5.
Points to Remember
Match Function is not case sensitive. It treats ABC and abc in the same way.
It works only with one dimensional arrays either vertical or horizontal.
If the lookup value occurs multiple times in look up array, It always returns the first exact match occurrence position of look up value.
Match Returns #N/A error if lookup value is not found in lookup array. Also, when the lookup array is not sorted in ascending or descending order when match type 1 or -1 is used.
Match works with only text length upto 255 characters in length.
Match Function is frequently used together with INDEX function as an alternative to VLOOKUP.
Suggested Reading
How to use the Index Function in Excel
Using VLOOKUP in excel – Complete Guide
Do you use Excel Match Function. Share your thoughts about Excel Match Function in comments below.
Match on Microsoft
Share this article with your Friends