The MATCH function in Excel 2010


In the following tutorial, you will be guided on the usage and the syntax of the MATCH function that is present in Excel 2010 Application.


Description

The function MATCH searches for a particular item in a list and returns the specific position of that value in the list. For example, if the cells B2:B5 contains values 10, 20, 30 and 40 and if you use the formula =MATCH(30,B2:B4), the formula returns the value 3, because the number ‘30’ is the third item in the list of values.


Syntax

The MATCH function has the following syntax and arguments

=MATCH(lookup_ value, lookup_array, [match_type])

Where

‘lookup_value’ is a required argument and it is the particular value in the lookup_array whose relative position wants to be returned by the MATCH function.

‘lookup_array’ is also a required argument and it is the array of items from which you want a particular item’s position to be returned by the MATCH function.

‘match_type’ is an optional argument and it can contain any of the values 1, 0 and -1. The match_type argument specifies how the Excel application searches or matches the lookup_value in the lookup_array. If this argument is ignored, the Excel program will use ‘1’ as the default value.
Refer the table below that describes how the Excel program searches or matches the lookup_value based upon different values supplied to the match_type argument.


Figure 1



Constraints

The MATCH function always returns the specific position of a value in the list and not the value itself. For instance, the formula =MATCH(“X”, {“Z”, “X”, “C”, “V”}, 0) returns ‘2’ because, the value “X” is in the second position in the list.

The MATCH function is not case-sensitive.

The MATCH function returns the error value #N/A if it does not find any match.


Example

Please refer the figure below for examples on MATCH function


Figure 2


So, this is how you can use the MATCH function in Excel application to find the relative position of a value in a given list of values.


See also the CHOOSE and the INDEX function in Excel 2010



Read Other Applications