How to use the Excel MATCH function

Match function in excel is used to find a position of the reference cell in a given range of cells, the reference is a value which is searched from a range of cells, and the position is the first position which is found for the value, the method to use this formula is as follows =MATCH( Value to be searched, Table, and exact or approx. match {0 or 1}).

Syntax: =MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

  • Lookup_value (required argument) – This is the value that we want to look up.
  • Lookup_array (required argument) – The data array that is to be searched.
  • Match_type (optional argument) – It can be set to 1, 0, or -1 to return results as given below:

1 or omitted: When the function cannot find an exact match, it will return the position of the closest match below the lookup_value. (If this option is used, the lookup_array must be in ascending order).

:  When the function cannot find an exact match, it will return an error. (If this option is used, the lookup_array does not need to be ordered).

-1: When the function cannot find an exact match, it will return the position of the closest match above the lookup_value. (If this option is used, the lookup_array must be in descending order).

Example 1: Let’s look at some Excel MATCH function examples and explore how to use the MATCH function as a worksheet function in Microsoft Excel:

Excel MATCH function - How to use the Excel MATCH function

1.1 Syntax:  =MATCH(72,K8:K13,1)

Result:

Excel MATCH function 1 - How to use the Excel MATCH function

Based on the Excel spreadsheet above, the following MATCH examples would return:

READ:  How to use RTD Function in Excel

Syntax: =MATCH(65,K8:K13,1)
Result: 2

Syntax: =MATCH(64,K8:K13,1)
Result: 1

1.2 Syntax:  =MATCH(72,K8:K13,)

Result:

Excel MATCH function 2 - How to use the Excel MATCH function

Based on the Excel spreadsheet above, the following MATCH examples would return:

Syntax: =MATCH(65,K8:K13,0)
Result: 2

Syntax: =MATCH(64,K8:K13,0)
Result: #N/A

1.3 Syntax:  =MATCH(72,K8:K13,– 1)

Result:

Excel MATCH function 4 - How to use the Excel MATCH function

Based on the Excel spreadsheet above, the following MATCH examples would return:

Syntax: =MATCH(58,K8:K13,– 1)
Result: 6

Syntax: =MATCH(62,K8:K13,– 1)
Result: 1

Note: Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.

Add a Comment

Your email address will not be published. Required fields are marked *