MATCH Function in Excel

Microsoft Excel offers the VLOOKUP and HLOOKUP functions to locate data in a large spreadsheet. However, these features come with certain limitations when it comes to complex data sets. For such times when data gets lost within the sheet, the MATCH function comes in handy. With MATCH, you can quickly locate any information, regardless of its position in the sheet. Whether dealing with large datasets or intricate structures, MATCH ensures swift data retrieval without the constraints of predefined lookup tables.

What is MATCH in Excel?

Excel's MATCH function allows us to find the numerical position of an element in a list of values. In simpler words, Excel gives us the number of the position in which the searched value is. The MATCH function is incredibly useful for quickly locating data points within large datasets or lists.

How Does MATCH in Excel Work?

The MATCH function in Excel efficiently locates specific data within a spreadsheet. Here's a concise breakdown:

• The MATCH function in Excel quickly determines the position of a specified value within a range.
• It requires three arguments: lookup_value (the value to find), lookup_array (the range of cells to search), and match_type (specifying exact or approximate match).
• It returns the relative position of the lookup_value within the lookup_array.
• Match_type options include 0 for an exact match, 1 for the largest value less than or equal to the lookup_value, and -1 for the smallest value greater than or equal to the lookup_value.
• This function facilitates efficient data retrieval and analysis in Excel.

By eliminating manual search efforts, MATCH enhances data retrieval efficiency, helping in quick decision-making and analysis.

Embark on your journey in MS Excel with our guide to the best colleges, innovative programs, online courses, and career opportunities!

MATCH: Syntax Explained

The match function has three arguments.

=MATCH(lookup_value, lookup_array, [match_type])

Where -

• lookup_value (Required): The value (number, text, or logical value)you want to match in lookup_array. 
• lookup_array (Required): The range of cells you want to search.
• match_type (Optional): Number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

Exact Match Example - Index Function in Excel

Suppose we want to find out the position of Emily's name in the list of employees:

We will set up our Excel formula. In cell G2, for instance, we'll input the following formula:

=MATCH("emily", A1:A10, 0)

Explanation of the formula:

• "emily": The value we are searching for.
• A1:A10: The range of cells containing the names of employees (column A).
• 0: Indicates an exact match.
• Excel will return the position of Emily's name in the list.
• If Emily's name is in the 4th row, Excel will return 4 as the result.

Use of MATCH in Excel

Finding a specific value in a long spreadsheet can be a challenging task. Although Excel includes a search function, the result cannot be processed further. 

If you want to know where a specific element is in the table and then translate the result into another formula, use other special functions. While the different search functions return the content of a specific cell based on a request, the MATCH function can indicate the position of the cell whose content has been searched.

The search result is then not displayed as the absolute value of a cell but in the form of a relative position, which means that the result always refers to the position within the range that has been searched.

Points to Remember

• The MATCH function allows you to find the relative position of a searched value in an array of values.
• When matching text values, the MATCH function doesn't differentiate between uppercase and lowercase letters.
• If the MATCH function fails to find a match for the lookup_value, it returns an "N/A!" error.
• The MATCH function can be used vertically or horizontally, depending on whether the range of cells is arranged in columns or rows.
• MATCH can perform both exact and approximate matches, for an exact match, ensure that the third argument of the MATCH function is set to 0.