MATCH

The MATCH function is used to search for a specified item in a range and return its relative position. It is commonly employed to find the position of a value within a list or array in Excel, facilitating data lookup and comparison tasks.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value The value to search for within the lookup_array.
lookup_array The range or array where the lookup_value will be searched.
match_type (Optional) Specifies the type of match to perform. It can be 1 (default) 0 or -1.

About MATCH

When you need to pinpoint the position of an item in a dataset, call upon the MATCH function in Excel. This versatile tool streamlines the search process, enabling swift identification of the relative location of a specific value within a given range or array. MATCH proves invaluable for tasks like data validation, index-matching, and cross-referencing in spreadsheets, enhancing efficiency in data analysis and comparison exercises.

Examples

Suppose you have a list of fruit names in cells A1:A5 (Apple, Banana, Cherry, Date, Fig) and you want to find the position of 'Date' within the list. The MATCH formula would be: =MATCH("Date", A1:A5, 0). This will return the position of 'Date' in the range A1:A5.

Imagine you have a set of numerical values in cells C1:C7 (5, 12, 18, 24, 31, 40, 50) and you wish to locate the position of the value 24 within the array. The MATCH formula would be: =MATCH(24, C1:C7, 0). This will output the position of 24 in the range C1:C7.

Questions

How does the MATCH function determine the relative position of the specified value?

The MATCH function scans the lookup_array to find the first occurrence of the lookup_value and returns its relative position within the array.

What does each match_type argument value signify in the MATCH function?

The match_type argument in the MATCH function specifies the type of match to perform. 1 (default) indicates an approximate match, 0 signifies an exact match, and -1 indicates the largest value less than or equal to the lookup_value.

Related functions

INDEX
VLOOKUP
HLOOKUP
OFFSET
LOOKUP
XLOOKUP