How To Use The Match Function in Microsoft Excel?

The MATCH Function is a Function available in Microsoft Excel. We use it to locate the position of a lookup value in a Row, Column or Table. When we use the MATCH Function, it searches for a specified item in a Range of Cells, and then returns the relative position of that item in an Array that matches a specified value in a specified order.

 

If You Want To Learn How To Use The Match Function in Microsoft Excel, read the post that follows.

We must select the Formulas tab and then locate the area of the ribbon named Functions Library.

Once the Functions Library is located, we select the category Lookup & Reference where we can see all the available Functions and select the MATCH Function. The Syntax of the Function is:

MATCH(lookup_value;lookup_array;match_type)

Once the Match Function is selected, the Function Arguments dialog box appears. In this dialog box we are notified that the Match Function Returns The Relative Position Of An Item In An Array That Matches A Specified Value In A Specified Order.

We have three different arguments that we must input, and which are:

Lookup_Value: “Is The Value We Use To Find The Value That We Want In The Array. A Number, Text Or Logical Value, Or A Reference To One Of These”

Lookup_Array: “Is A Contiguous Range Of Cells Containing Possible Lookup Values, An Array Of Values, Or A Reference To An Array”

 

Match_Type: “Is A Number 1, 0, Or -1 Indicating Which Value To Return”. The default Value for this Argument is 1

In the Lookup_value argument box, I have entered the number 789 that I want to be found.

In the Lookup_array argument box, I have selected the area for where I want to search and from where the Result to be extracted.

In the Match_type argument box, I have entered the Argument 1.

If the argument is 1 or omitted, the Function MATCH finds the largest value that is less than or equal to the Lookup_value. The values in the Lookup_array argument must be placed in ascending order.

If the argument is 0, the Function MATCH finds the first value that is exactly equal to the Lookup_value. The values in the Lookup_array argument can be in any order.

If the argument is -1, the Function MATCH finds the smallest value that is greater than or equal to the Lookup_value. The values in the Lookup_array argument must be placed in descending order,

The MATCH Function will find the result in the Table_array, the number 789.

The result will be 3, because the Lookup_value is located in the fourth Cell of the Lookup_array.

The MATCH Function will try to find the closest result in the Table_array by looking up the number 550.

The result is #N/Y, because the data in the Table_array are not placed in a descending order because in the Match_type argument, I typed -1.

Just make sure to set Match_type to zero (0) if you need an exact match.

 

Go ahead can check out the video describing How To Use The Match Function in Microsoft Excel

and …

To Find Out More About Microsoft Office Applications You Can Subscribe To My YouTube Channel!!!