Excel MATCH Function: Arguments and Match Types

Formulas & functions · Updated November 18, 2026

The Excel MATCH function takes a value to find, a one-dimensional range to search, and an optional match type. Use 0 for an exact match, 1 for an approximate match in ascending order, or -1 for an approximate match in descending order. With the lookup value in E2 and items in A2:A10, =MATCH(E2,A2:A10,0) returns the item’s position in that range; if the matching item is third, the result is 3.

What the MATCH Function Returns

MATCH returns a position, not the contents of the matching cell. If it finds a value in the fifth cell of the range you gave it, the result is 5, even if that cell is on worksheet row 20. The count starts at the first cell of the lookup range, not at row 1 of the worksheet.

That distinction is useful when you need to locate an item before retrieving information associated with it. For example, MATCH can find which row contains a product name, and INDEX can use that position to return the product’s price. MATCH can also help build formulas that adapt when you insert or reorder columns, rather than relying on a fixed column number.

The lookup range must be a single row or a single column. A range such as A2:A20 or A2:F2 is suitable; a rectangular range such as A2:C20 is not. If a value appears more than once, MATCH returns the position of its first match in the search direction.

The Three MATCH Arguments

Excel documentation calls the inputs to a function arguments. MATCH has three, with the third optional:

  • lookup_value: The value you want to locate. It can be a number, text, a cell reference, or a logical value.
  • lookup_array: The one-row or one-column range to search. It can contain worksheet cells or, in some cases, values supplied directly in a formula.
  • match_type: The rule Excel uses to compare values. Its supported settings are 1, 0, and -1. If you leave it out, Excel uses 1.

For a typical search for a particular label, product code, or name, specifying 0 is the safest choice. It tells Excel to look for an exact match and does not require the list to be sorted. Leaving the third argument blank is not equivalent to exact matching: Excel treats it as 1, which expects an ascending list and may return a nearby value instead.

How the Match Types Work

0 means exact match. Excel searches for the lookup value and returns its position if it finds one. The values do not need to be sorted. If no exact match exists, the result is #N/A. MATCH with 0 is generally the right setting for unsorted lists and identifiers, such as employee names, order numbers, or category labels.

1 means approximate match, with an ascending lookup array. Excel returns the position of the largest value that is less than or equal to the lookup value. The list must be sorted in ascending order, from smallest to largest. If the lookup value is smaller than the first value, there is no suitable result.

-1 means approximate match, with a descending lookup array. Excel returns the position of the smallest value that is greater than or equal to the lookup value. The list must be sorted in descending order, from largest to smallest. This setting can be useful for threshold tables arranged from high to low.

Approximate matching is useful for ranges and breakpoints, not just for finding values that look similar. A tax-rate or shipping table might define a result for each lower bound: a value of 75 belongs to the highest threshold that does not exceed 75. The order requirement matters: if the list is unsorted, approximate matching may return an unexpected answer. Verify the sort order before using 1 or -1.

Examples for Exact and Approximate Searches

Suppose A2:A6 contains North, East, South, West, and Central, in that order, and E2 contains South. With that layout, =MATCH(E2,A2:A6,0) returns 3. South is the third entry in the specified range, even though its worksheet row is 4. This is a common reason a MATCH result can appear one row lower or higher than expected: the result is relative to the first cell of the range.

For an approximate example, suppose A2:A6 contains the ascending numbers 0, 60, 70, 80, and 90, and E2 contains 75. With this layout, =MATCH(E2,A2:A6,1) returns 3. The largest value in the range that is less than or equal to 75 is 70, which is the third item. If the list instead contains 100, 90, 80, 70, and 60 in descending order, and E2 is 75, then =MATCH(E2,A2:A6,-1) returns 3: the smallest listed value at least 75 is 80, also the third item.

These examples show why the approximate result is not necessarily an exact copy of the lookup value. MATCH reports the position of the qualifying entry. If you need to display that entry itself, use a separate lookup or INDEX formula.

Combining MATCH with INDEX

INDEX and MATCH are often paired to return a value from a related range. Imagine A2:A6 contains product names, B2:B6 contains their prices, and E2 contains a product name that appears in column A. With that layout, =INDEX(B2:B6,MATCH(E2,A2:A6,0)) finds the product’s position in A2:A6 and returns the value at the same position in B2:B6. If the matched product is in the third entry and its price is 18.50, the formula returns 18.50.

The two ranges need to line up: the first item in the names range must correspond to the first item in the prices range, and so on. The exact-match setting is usually appropriate for names and product codes. If the name in E2 is missing from A2:A6, MATCH returns #N/A and the combined formula cannot return a corresponding price. That can be handled with an error-handling formula if the workbook needs a custom message, but it is worth checking the source data first.

Text Matching, Wildcards, and Case

MATCH does not distinguish between uppercase and lowercase letters when comparing text. For example, an exact search for apple can match Apple. If capitalization is meaningful in your data, MATCH alone is not a case-sensitive search method; a formula designed for case-sensitive comparison is needed.

With match_type set to 0, MATCH supports wildcards in text searches. An asterisk represents any sequence of characters, while a question mark represents one character. A tilde can escape a wildcard when you want to search for the literal symbol rather than use it as a pattern. These wildcard rules apply to text matching; they are not a way to make approximate numeric matching more flexible.

Wildcards can help when you know only part of a label, but broad patterns may match an earlier entry you did not intend. For instance, a pattern matching all text that begins with a common prefix could find the first of several similar names. Check the range and the first-match behavior if a wildcard search returns a surprising position.

Common Errors and How to Diagnose Them

The most common result to investigate is #N/A, which means MATCH did not find a qualifying value. In an exact search, compare the lookup value with the source entries. Extra spaces, different punctuation, numbers stored as text, and slightly different spellings can make values that look alike behave differently. Check whether the searched range includes the expected cell and whether the value is actually present.

An unexpected position can have several causes. Confirm that the formula uses the intended start and end cells, and remember that the answer counts from the top or left of the lookup range. If you chose 1 or -1, confirm the list is sorted in the required direction and that the lookup value falls within the range covered by the thresholds. For a repeated exact value, the first matching occurrence is returned.

  • For a normal exact search, check that the third argument is 0.
  • For approximate matching, sort the lookup array in the direction required by the selected match type.
  • Check whether numbers or dates are stored in a different data type from the lookup value.
  • Inspect the lookup range for leading or trailing spaces and duplicate entries.
  • Test the MATCH formula by itself before troubleshooting a longer formula that uses it.

A formula can return a valid position that is still wrong for the task if its ranges are misaligned. In an INDEX-and-MATCH formula, check that the lookup range and the return range cover corresponding rows or columns. If you wrap the result in an error-handling function, avoid hiding every error before confirming that the lookup logic and source data are correct.

MATCH Compared with XMATCH

XMATCH is a newer alternative available in Microsoft 365 and Excel 2021 or later. Like MATCH, it returns a relative position, but it defaults to exact matching and offers additional search options, including searching from the last item toward the first. This can make XMATCH more convenient when an exact match is wanted or when the most recent occurrence is important.

MATCH remains useful for compatibility with older Excel workbooks and for formulas that already use its established match_type behavior. Neither function returns the matched cell’s value by itself; use INDEX or another appropriate function when you need a result from a separate range. XLOOKUP is another option in Microsoft 365 and Excel 2021 or later when the goal is to return a related value directly rather than calculate a position.

Frequently Asked Questions

What does the third parameter in Excel MATCH mean?
The third argument, match_type, sets the comparison rule. Use 0 for an exact match, 1 for approximate matching in ascending order, or -1 for approximate matching in descending order. If omitted, it defaults to 1.

Does MATCH return the row number?
It returns the position within the lookup range, not the worksheet row number. If the match is the third item in A2:A10, MATCH returns 3, even though that item is on worksheet row 4.

Why does MATCH return #N/A?
Usually, the requested value is not found under the selected match rule. Check the range, spelling, spaces, data types, and match_type. For approximate matching, also verify that the values are sorted in the required direction.

Does MATCH need a sorted list?
No, not when match_type is 0. Sorting is required for reliable approximate searches with 1 or -1: ascending for 1 and descending for -1.

Is MATCH case-sensitive?
No. MATCH treats uppercase and lowercase text as equivalent. Use a different formula approach if distinguishing capitalization is essential.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.