MATCH Function in Excel

MATCH Function in Excel

3 mins readComment
Rashmi
Rashmi Karan
Manager - Content
Updated on Feb 9, 2024 16:43 IST

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.

MATCH in Excel

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.

INDEX function in Excel
INDEX function in Excel
The INDEX function in Excel is a very powerful formula within the search and reference functions. The INDEX function is considered the predecessor of VLOOKUP and HLOOKUP. This function has...read more

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 collegesinnovative programsonline 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.
How to Create MIS Report in Excel?
How to Create MIS Report in Excel?
A Management Information System (MIS) report in Excel is a vital tool for businesses and organizations, offering valuable insights by consolidating and presenting data in an organized and comprehensible format....read more
How to Merge Cells in Excel – Shiksha Online
How to Merge Cells in Excel – Shiksha Online
Learn how to merge cells in Excel through real datasets.

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)

Match function 1

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.
Match function 2

Index and Match in Excel – Shiksha Online
Index and Match in Excel – Shiksha Online
INDEX and MATCH in Excel are the functions that can solve your problem when you can’t use the VLOOKUP function to perform the search you need. The article talks about...read more

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.

How to Import Text Files to Excel
How to Import Text Files to Excel
Managing massive volumes of data is tricky, especially when those are on your text files. Moving data to Excel for better calculations and presentations is an efficient way to handle...read more
Tutorial – XLOOKUP in Excel – Shiksha Online
Tutorial – XLOOKUP in Excel – Shiksha Online
The article talks about an advanced and flexible lookup function, XLOOKUP.

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.
About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio