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

Most widely used in conjunction with INDEX function. Wildcard characters can be used when lookup value is a text string. Match is not case-sensitive. Match returns the #N/A error if no match is found. Match type (match_type) specifies how excel matches the item in a list. When Match Type is 0 This is the exact match criteria. It returns the first exact match position (or an error if there is no match). When Match Type is 1 (default) The data must be sorted in the ascending order for this option. It returns the largest value equal to or less than the lookup value. When Match Type is -1 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.

Excel MATCH Function – Live Example

Excel MATCH Function – Video Tutorial

Excel VLOOKUP Function. Excel HLOOKUP Function. Excel INDEX Function. Excel INDIRECT Function. Excel OFFSET Function.

VLOOKUP Vs. INDEX/MATCH Using Excel INDEX MATCH Combo (with examples) Find the Closest Match of a Value in Excel. Find the Last Occurrence of an Item in a List in Excel. Get Unique Items from a List in Excel.