Excel MATCH Function: Syntax, Examples, and Common Errors

Formulas & functions · Updated November 18, 2026

In Excel, MATCH is a function—not a separate statement—that returns the position of a value in a one-dimensional range. For an exact search, use =MATCH(D2,A2:A6,0); if the value in D2 is the third item in A2:A6, the result is 3. MATCH returns a position, not the matching value itself.

What the MATCH function does

MATCH searches a row or column and tells you where the first matching item occurs within that range. Its result is a relative position, so the first cell in the search range is position 1 even if it is worksheet cell A10. This distinction is useful when you need to locate a row, combine the position with another function, or check whether a value appears in a list.

For example, if A2:A6 contains five product codes, a result of 4 means the match is in the fourth cell of that range, A5. It does not mean the result is worksheet row 4. MATCH can search numbers, text, dates, or logical values, provided the lookup value and the values in the range are comparable.

The function pattern is MATCH(lookup_value, lookup_array, [match_type]). The square brackets indicate that match_type is optional. In practice, supplying it explicitly prevents surprises, especially because Excel assumes approximate matching if you leave it out.

Exact matches and the match_type argument

For most everyday searches, set match_type to 0. That tells MATCH to look for an exact match and means the lookup range does not need to be sorted. With product codes in A2:A6 and the code to find in D2, this formula returns the item’s position within A2:A6:

With product codes in A2:A6 and the lookup code in D2, =MATCH(D2,A2:A6,0) returns 3 if the code in D2 is the third item in the range.

There are three match_type settings:

  • 0 finds the first exact match. Use it for names, IDs, codes, and unsorted lists.
  • 1 finds the largest value that is less than or equal to the lookup value. The range must be sorted in ascending order.
  • -1 finds the smallest value that is greater than or equal to the lookup value. The range must be sorted in descending order.

Exact matching is generally the safest choice unless you specifically want an approximate result. If you omit match_type, Excel uses 1, which can return a plausible but incorrect position when the range is unsorted. A formula may calculate without showing an error and still produce the wrong answer, so include the final 0 when you expect an exact match.

Approximate matches and sorted ranges

Approximate matching is useful when the range represents ordered thresholds, such as commission bands, grade cutoffs, or shipping brackets. With match_type 1, MATCH assumes the values are in ascending order. For example, a sorted list of lower bounds can identify the band that applies to a value. With match_type -1, the list must be in descending order instead.

Suppose A2:A5 contains the ascending thresholds 0, 50, 100, and 200, and D2 contains 130. With those values in place, =MATCH(D2,A2:A5,1) returns 3: 100 is the largest threshold that does not exceed 130. The position can then be used to select the corresponding label or rate from a parallel range.

Sorting is not a cosmetic requirement for these modes. If the range is in the wrong order, MATCH may return an unexpected position. Before using approximate matching, check that every value is in the required order and that the threshold logic matches your intended boundaries. If you need exact matching, use 0 instead and avoid depending on sort order.

Using MATCH with wildcards

When match_type is 0 and the lookup value is text, MATCH can use wildcards. The asterisk (*) represents any number of characters, and the question mark (?) represents one character. A tilde (~) lets you search for a literal asterisk or question mark rather than treating it as a wildcard.

With text entries in A2:A6 and a search pattern in D2, =MATCH(D2,A2:A6,0) finds the first entry that fits the pattern if D2 contains a wildcard pattern such as North*. The result is still a position, not the matching text. This can help locate a code with a known prefix, but it may match more than one entry; MATCH returns the first one in the range.

Wildcards do not make a text search case-sensitive. If the distinction between uppercase and lowercase matters, MATCH alone is not sufficient; a case-sensitive approach requires a different formula design. Also check for extra spaces in source data, since a visually similar value with a leading or trailing space may not match as expected.

Returning the matching value with INDEX

A frequent misconception is that MATCH returns the value it finds. It returns only a position. To retrieve a related result from another column, combine MATCH with INDEX. For instance, if product codes are in A2:A6, prices are in B2:B6, and the requested code is in D2, use:

With product codes in A2:A6, corresponding prices in B2:B6, and the requested code in D2, =INDEX(B2:B6,MATCH(D2,A2:A6,0)) returns the price from the row containing that exact code. If the matching code is the third item in A2:A6 and the third price is 18, the result is 18.

The two ranges must line up: the first code in A2:A6 must correspond to the first price in B2:B6, and so on. MATCH supplies the relative position; INDEX uses that position in the prices range. This pattern is flexible because the result range can be to the left or right of the lookup range, unlike older VLOOKUP arrangements that require the lookup column to be at the left edge of the table.

Common MATCH errors and troubleshooting

  • #N/A: An exact match was not found, or the value differs from the list because of a typo, extra space, inconsistent data type, or different punctuation. Check the value and the lookup range.
  • Unexpected position: Check whether match_type was omitted. Excel then uses approximate matching, which requires sorted data.
  • Wrong result with approximate matching: Confirm the sort direction and that the selected threshold is the one your rules require.
  • Only one result appears: MATCH returns a single position, and duplicate entries do not produce a list of all positions. It identifies the first matching occurrence.
  • Text that looks like a number: A number stored as text may not behave like a numeric value. Check the source cells and standardize the data before relying on the lookup.

If you want a friendly message instead of #N/A, you can wrap the search in IFNA. With codes in A2:A6 and the lookup code in D2, =IFNA(MATCH(D2,A2:A6,0),"Not found") returns the position when a match exists and the text Not found when it does not. This handles the missing-match case, but it does not fix an incorrectly sorted approximate lookup or other data problems.

MATCH compared with XLOOKUP

MATCH remains useful when you need a position, especially as part of an INDEX/MATCH formula or as an input to other calculations. XLOOKUP is often more direct when the goal is to return a related value: it can search one range and return a corresponding item from another range in one function, and exact matching is its default behavior.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, while MATCH is supported across many older Excel releases. If a workbook must work for people using earlier versions, MATCH with INDEX may be the more compatible option. Choose based on the workbook’s audience and the task: use MATCH when the position itself matters, and consider XLOOKUP when you want a simpler lookup-and-return formula.

Frequently Asked Questions

Is MATCH an Excel function or a statement?
MATCH is a worksheet function. It is entered in a cell as a formula and returns the position of an item in a range; it is not a VBA statement or a standalone command.

How do I make MATCH look for an exact value?
Set the third argument, match_type, to 0. For example, with the lookup value in D2 and the list in A2:A6, =MATCH(D2,A2:A6,0) searches for an exact match.

Why does MATCH return a number instead of the matching text?
That number is the item’s relative position within the lookup range. Combine MATCH with INDEX if you want to return a value from a corresponding range.

Does MATCH distinguish uppercase from lowercase?
No. A standard MATCH text search is not case-sensitive. Use another formula design if uppercase and lowercase must be treated as different values.

Can MATCH return every duplicate match?
No. MATCH returns one position: the first matching occurrence in the range. To list multiple matching records, use a filtering approach rather than MATCH alone.

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.