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])

Arguments

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

About

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.

Tips & notes

Ensure that the lookup_value you are searching for is present within the lookup_array. The MATCH function can be particularly useful for tasks where you need to sort or compare data elements across different sets or lookup tables.

Common 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.