Excel MATCH Expressions: Syntax and Examples

Formulas & functions · Updated November 18, 2026

Excel’s MATCH function finds a value in a one-dimensional range and returns its position within that range—not the value itself. For an exact search, with the values 10, 20, 30, 40, and 50 in A2:A6 and 30 in D2, enter =MATCH(D2,A2:A6,0); the result is 3 because 30 is the third item in the lookup range.

What MATCH Does in Excel

MATCH answers a position question: where does this value occur in this row or column? That position is counted from the start of the range, not from the worksheet’s row or column numbering. If the match is in A4, for example, its position in A2:A6 is 3, even though the worksheet row number is 4.

This distinction is useful when you need to locate a record, select a column, or give another function a relative position. MATCH does not return a neighboring label or a value from a separate column by itself. It identifies where the lookup value occurs, and you can use that position in a larger calculation.

The lookup range must be a single row or a single column. A two-dimensional block such as A2:C10 is not a suitable lookup_array for MATCH. For a vertical list, use a column range; for a horizontal list, use a row range.

MATCH Arguments and Search Modes

The function takes a value to look for, a one-dimensional range to search, and an optional match type. The match type controls whether Excel looks for an exact match or uses an ordered list to find an approximate one. If you leave this argument out, MATCH uses 1, which means approximate matching against data sorted in ascending order.

  • 0: Find the first exact match. The lookup range does not need to be sorted.
  • 1: Find the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order. This is the default when the argument is omitted.
  • -1: Find the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

When you are unsure which mode applies, use 0 for an exact search. It avoids the most common MATCH mistake: leaving out the match type and getting an unexpected approximate result from an unsorted list.

Finding an Exact Match

Exact mode is appropriate for most searches involving product codes, names, IDs, dates that must match precisely, and other values where a near match is not acceptable. With 10, 20, 30, 40, and 50 in A2:A6, and 30 in D2, =MATCH(D2,A2:A6,0) returns 3. If the value in D2 is absent from the list, Excel returns #N/A rather than a position.

MATCH returns the first occurrence when an exact value appears more than once. With the entries Oak, Pine, Oak, and Maple in A2:A5, and Oak in D2, =MATCH(D2,A2:A5,0) returns 1. It does not report every occurrence or distinguish between duplicate records. If duplicates matter, first decide whether the first occurrence is sufficient or whether you need a method that returns multiple results.

Text matching is not case-sensitive: a search for north can match North. If capitalization is meaningful in your data, ordinary MATCH is not a case-sensitive search tool. Also check for spaces, inconsistent punctuation, and values stored as text in one place but as numbers in another; entries that look alike on screen may not be equivalent to Excel.

Approximate Matches and Sorted Lists

Approximate matching is useful for finding the band or threshold that applies to a value, such as a shipping bracket or a grading scale. For an ascending list, MATCH with type 1 returns the position of the largest listed value that is less than or equal to the lookup value. With 10, 20, 30, 40, and 50 in A2:A6, and 35 in D2, =MATCH(D2,A2:A6,1) returns 3: 30 is the largest listed number not greater than 35.

The sorting requirement is essential. If the same values are in an arbitrary order, type 1 can return an incorrect position. Excel does not automatically sort the range for you. Before using approximate matching, confirm both the direction and the actual order of the data. A range with blanks, text mixed into numeric values, or an accidental out-of-order entry can also make the result difficult to interpret.

Type -1 works in the opposite direction and requires descending order. With 50, 40, 30, 20, and 10 in A2:A6, and 35 in D2, =MATCH(D2,A2:A6,-1) returns 2 because 40 is the smallest value in that descending list that is greater than or equal to 35. If the lookup value falls outside the range’s thresholds, consider whether the result is meaningful for your task before using it in a report or calculation.

Combining MATCH with INDEX

MATCH is often paired with INDEX to find a value in one column and return a related value from another. Suppose product IDs C-101 through C-104 are in A2:A5, product names are in B2:B5, and D2 contains C-104. With those columns and that lookup value, =INDEX(B2:B5,MATCH(D2,A2:A5,0)) returns the product name in the fourth position of B2:B5. If the fourth name is Notebook, the result is Notebook.

The two functions have separate jobs: MATCH finds the relative position of the ID, and INDEX returns the item at the same position in the names range. The ranges should line up in size and order. If one range starts on a different record or includes an extra row, the returned value may belong to the wrong item even though the expression calculates without an error.

This combination can be more flexible than a lookup tied to a fixed left-to-right layout. You can search one column and return data from a column to its left or right, provided the ranges correspond to the same records. It can also make a model easier to adjust when the return column changes, since the INDEX range is explicit.

Text Searches and Wildcards

In exact mode, MATCH supports wildcards for text searches. An asterisk represents any number of characters, including none, and a question mark represents one character. For example, if A2:A5 contains North, Northeast, South, and West, and D2 contains Nort*, =MATCH(D2,A2:A5,0) returns 1 because North is the first entry that fits the pattern. Since the first matching item is returned, changing the order of the list can change the result.

To search for a literal asterisk or question mark rather than use it as a wildcard, place a tilde before the character in the search text. Wildcards apply to text matching; they are not a way to make approximate numeric searches behave differently. Use the appropriate match type for the kind of data you are searching.

For a partial-text search, consider whether several entries might fit the pattern. A wildcard search can successfully return a position while still selecting a record you did not intend, simply because that record appears first. If the data contains similar names or codes, use a more specific lookup value or add a separate check to confirm the selected record.

Common MATCH Problems and Checks

The most familiar error is #N/A, which means MATCH could not find a suitable item under the chosen match rules. In exact mode, check whether the value actually exists, whether there are leading or trailing spaces, and whether the lookup value and list entries have compatible data types. In approximate mode, check the required sort order and whether any values meet the search condition.

A result that looks like the wrong answer often points to an omitted match type. If you intended exact matching but left the third argument out, MATCH uses approximate ascending mode. The formula may still return a number, so the result can appear plausible even when it refers to the wrong entry. Stating 0 explicitly is a good habit for exact lookups.

  • Confirm that the lookup range is only one row or one column.
  • Check that the match type matches the task, especially when using approximate matching.
  • For approximate searches, verify the list’s sort direction and every value’s position.
  • Check for hidden spaces, text-versus-number differences, duplicate entries, and inconsistent spelling.
  • Remember that the result is a position within the supplied range, not a worksheet row number.

If you need to display a friendlier message when no match exists, you can handle the #N/A result with an error-handling function. Make sure the alternative message does not conceal a data problem that should instead be corrected.

MATCH Compared with XMATCH and Other Lookups

MATCH remains useful because it works in many long-standing Excel versions and pairs naturally with INDEX. XMATCH is a newer alternative available in Excel for Microsoft 365 and Excel 2021. It uses exact matching by default and offers additional search options, including searching from the last item toward the first. That can be helpful when duplicates exist and the last occurrence is the one you need.

XLOOKUP, also available in Excel for Microsoft 365 and Excel 2021, is often a more direct choice when the goal is to return a related value rather than a position. MATCH is still a sensible choice when you specifically need the relative position, when you are maintaining an existing workbook, or when you want to combine that position with INDEX. The best option depends on whether the task is to locate a position, return a value, or handle a workbook that must work in older Excel versions.

Function availability can differ in older editions of Excel. If a workbook will be shared with people using earlier versions, check that their software supports any newer lookup functions you use. MATCH is broadly compatible, which is one reason older spreadsheets continue to use it.

Frequently Asked Questions

Does MATCH return the matching value?
No. MATCH returns the item’s position within the lookup range. Pair it with INDEX when you want to return a related value from another range.

What does 0 mean in MATCH?
It sets the function to search for an exact match. Use it for ordinary lookups where a nearby value is not acceptable.

Why does MATCH return the wrong position?
A common reason is that the match type was omitted, so Excel used approximate ascending matching. Check the third argument, sort order, duplicates, and whether the value is stored consistently.

Can MATCH search for part of a word?
Yes. In exact mode, text search values can use an asterisk for any number of characters or a question mark for one character. The first item matching the pattern is returned.

Is MATCH case-sensitive?
No. Standard MATCH does not distinguish uppercase from lowercase. Use another approach if capitalization must be treated as significant.

What is the difference between MATCH and XMATCH?
Both can find positions in a range. XMATCH is newer, defaults to exact matching, and provides extra search options; MATCH is more widely compatible with older Excel versions.

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.