MATCH function

Introduction

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

When to use Excel MATCH Function

Excel MATCH function can be used when you want to get the relative position of a lookup value in a list or an array.

What it Returns

It returns a number that represents the position of the lookup value.

Syntax

= MATCH(lookup_value, lookup_array, [match_type])

Input Arguments

• lookup_value – The value for which you are looking for a match in the lookup_array.
• lookup_array – The range of cells in which you are searching the lookup_value.
• [match_type] – (Optional) This specifies how excel should look for a matching value. It can take three values -1, 0 , or 1 (read additional notes below for more info).

Additional Notes

• When Match Type is 0 (Exact match): This is the exact match criteria. It returns the first exact match position (or an error if there is no match). The array can be sorted in any order.
• When Match Type is 1 (Less than): This will find the largest value that is less than or equal to the value. The data must be sorted in the ascending order for this option. Note that this is the default value of the MATCH function, so, if the match_type parameters is omitted, it assumes that the match_type to be 1.
• When Match Type is -1 (Greater Than): This will find the smallest value that is greater than or equal to the value. The data must be sorted in the descending order for this option. It returns the smallest value equal to or greater than the lookup value.

Video