In Excel, MATCH accepts a lookup value, a one-dimensional lookup array, and an optional match type: 0 requests an exact match, 1 finds an exact match or the next smaller value in ascending data, and -1 finds an exact match or the next larger value in descending data. The function returns the value’s position within the array, not the value itself.
What MATCH does
MATCH is a lookup function for locating an item in a row or column. Its result is a relative position: if the match is the third cell in the range, MATCH returns 3, even if that range begins at worksheet row 20. This position is useful on its own—for example, to identify a ranking—or as an input to INDEX, which can return a value from another range.
A common misunderstanding is that MATCH returns the matching cell or its contents. It does not. If a product name is found in the fourth position of a list, the result is the number 4. To retrieve a related price or description, combine MATCH with another function, or use a lookup function that returns a value directly.
Input arguments and accepted types
MATCH takes three arguments in this order: the value to find, the row or column to search, and an optional match type. The first argument can be a number, text, a logical value such as TRUE or FALSE, or a reference to a cell containing a value. The lookup array should be a single row or a single column; a rectangular table is not a suitable search array for a single MATCH operation.
- Lookup value: The item Excel should search for. It can be typed into the formula or referenced from a cell.
- Lookup array: The one-dimensional range or array containing the possible matches. It can be a vertical list or a horizontal row.
- Match type: The optional number 1, 0, or -1, which determines whether Excel uses approximate or exact matching. If you omit it, Excel uses 1.
For example, with product names in A2:A6 and “Notebook” as the item being searched, this formula returns the position of that name in the list:
With product names in A2:A6, where Notebook is the third item: =MATCH("Notebook",A2:A6,0) returns 3.
The range’s shape matters. A vertical range such as A2:A6 and a horizontal range such as B1:F1 are both appropriate. If you need to search a two-dimensional table, use separate MATCH operations to locate a row and a column, then use INDEX to return the intersecting value.
How the three match types work
0 means exact matching. Excel searches for the specified value without requiring the list to be sorted. If it cannot find a match, the result is usually #N/A. This is generally the safest choice for names, product codes, IDs, and other categories where “close enough” would be wrong. Exact text matching is not case-sensitive: for instance, Excel treats “north” and “North” as the same for MATCH.
1 means exact or next smaller. Excel returns the position of an exact match if there is one; otherwise it finds the largest value that is less than the lookup value. The array must be sorted in ascending order for this approximate search to give dependable results. Ascending means smallest to largest, such as 10, 25, 60, 80, 100.
-1 means exact or next larger. If there is no exact match, Excel finds the smallest value that is greater than the lookup value. The array must be sorted in descending order, such as 100, 80, 60, 25, 10. This mode is less commonly needed, but can help find the next applicable threshold in a descending list.
Leaving out the third argument does not mean “exact.” It makes MATCH behave as though you supplied 1, which assumes ascending data. For an unsorted list or a list of labels, explicitly use 0 unless approximate matching is intentional.
Practical examples
Suppose A2:A6 contains the sorted numbers 10, 25, 60, 80, and 100. With 72 as the lookup value and an ascending list in A2:A6, =MATCH(72,A2:A6,1) returns 3. The third item is 60, the largest value below 72. It does not return 60; it returns 60’s position.
For descending data, suppose A2:A6 contains 100, 80, 60, 25, and 10. With 72 as the lookup value and that descending list in A2:A6, =MATCH(72,A2:A6,-1) returns 2, the position of 80, the smallest list value that is still greater than 72.
MATCH can also locate a row and a column independently. For example, assume A2:A6 contains region names, B1:D1 contains month names, and B2:D6 contains the corresponding values. With that layout, =INDEX(B2:D6,MATCH("North",A2:A6,0),MATCH("March",B1:D1,0)) finds the row for North and the column for March, then returns the value at their intersection. The two MATCH results are relative positions within their respective ranges, which is exactly what INDEX needs.
For exact text searches, match type 0 also permits wildcards. An asterisk represents any number of characters, and a question mark represents one character. A tilde escapes a wildcard when you want to search for the literal character. For example, with labels in A2:A6, =MATCH("Pen*",A2:A6,0) finds the first text entry that begins with “Pen.” Wildcards are useful for partial text searches, but are not a substitute for exact matching when searching numeric IDs.
Numbers, text, dates, and logical values
Numbers stored as text are a frequent source of surprising lookup results. A cell that displays 123 may contain the numeric value 123 or the text string “123.” They can look identical on the sheet while behaving differently in calculations and lookups. If a search unexpectedly returns #N/A, check whether the lookup value and the list entries use the same underlying type. Convert or clean the source data consistently rather than relying on Excel to interpret mixed values as equivalent.
Dates in Excel are stored as serial numbers, while the cells are usually formatted to display calendar dates. MATCH can search dates, but the lookup date and the list should contain comparable date values, not a mixture of real dates and date-looking text. Times are also represented numerically. A date or time that appears identical on screen can still differ internally, so formatting alone does not prove two values are equal.
Text matching with match type 0 ignores letter case, but spaces and other characters still matter. A trailing space in “North ” can prevent a match with “North.” Imported data may also contain nonbreaking spaces or other invisible characters. Functions such as TRIM can help with ordinary extra spaces, though imported nonbreaking spaces may need additional cleanup. When preparing an important lookup list, inspect the source values rather than assuming the displayed text tells the whole story.
Common errors and how to troubleshoot them
The most familiar failure is #N/A, which means Excel did not find a match under the chosen rules. With match type 0, check spelling, spaces, data type, and whether the lookup value actually occurs in the specified range. With match type 1 or -1, check both the sort direction and whether the requested value falls within the range of available values. Approximate matching does not make an unsorted list safe.
- Check the match type. Add 0 for an unsorted list or an exact category lookup; do not accidentally rely on the default.
- Check the range. Make sure it covers the intended entries and is a single row or column.
- Check sorting. Mode 1 needs ascending order; mode -1 needs descending order.
- Check the underlying values. Compare numbers with numbers, dates with dates, and clean text with clean text.
- Check errors in the source. Error values in lookup data can interfere with calculations, so inspect and correct source errors when results are unexpected.
If you wrap MATCH in IFNA to show a message instead of #N/A, first make sure the formula is configured correctly. Hiding an error can make a workbook easier to read, but it can also conceal a misspelled key or an incorrectly sorted approximate-match list.
MATCH compared with XMATCH and other lookup options
MATCH remains useful when a workbook needs a position rather than a returned value, or when compatibility with older Excel files matters. It also pairs naturally with INDEX for two-way lookups. In Microsoft 365 for Windows, XMATCH is a newer alternative with exact matching as its default and additional search options. XMATCH is available in Microsoft 365 and Excel 2021 or later; MATCH is the more compatible choice for files that may be opened in older releases.
XLOOKUP is often more direct when the goal is to return a related value from another range. It can search one range and return a result from another without requiring the caller to combine a position with INDEX. However, MATCH is still valuable when the position itself is needed, such as selecting a column dynamically or building a flexible INDEX formula. Choose based on the result you need and the Excel versions that will use the workbook.
Frequently Asked Questions
What is the default match type in Excel MATCH?
If you omit the third argument, MATCH uses 1. That is an approximate search that assumes ascending order, so provide 0 when you want an exact match.
Does MATCH return the matched value?
No. MATCH returns the relative position of the match within its lookup array. Use INDEX with MATCH if you need to return a value from a related range.
Does MATCH distinguish uppercase and lowercase?
No. MATCH is not case-sensitive. If capitalization must distinguish otherwise identical text, use a case-sensitive approach such as combining EXACT with an array calculation.
Can MATCH search for part of a text value?
Yes. With match type 0, use an asterisk for any number of characters or a question mark for one character. Use a tilde before a wildcard character when you need to search for that character literally.
Why does MATCH return #N/A when the value looks present?
The lookup value may differ from the list entry because of a text-versus-number mismatch, hidden spaces, a different date value, or a range that omits the entry. For approximate matching, an incorrect sort order can also cause an unreliable result.
Choosing a reliable setup
For most everyday lookups—names, codes, labels, or unsorted lists—use exact mode and make sure the lookup value and list are consistently prepared. Reserve approximate modes for deliberate threshold searches, sort the list in the required direction, and verify what “next smaller” or “next larger” means for the task. Remember that MATCH reports a position, not a result value; that distinction makes it easier to build dependable formulas and to diagnose unexpected output.
Related
- Excel Timesheet for Multiple Employees: Setup and Tips
- Excel Invoice Bill Format: Build a Useful Template
- Excel Accounting Solver: What It Does and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- DAX SUMPRODUCT: Multiply and Sum Values in Power BIDAX does not have a built-in SUMPRODUCT function. To multiply values row by row and add the results,…
- Sort Excel Alphabetically: Rows, Columns, and ListsTo sort a list alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column,…
- Rank in Excel by Multiple Criteria: Formulas and TipsWith primary scores in B2:B5, secondary scores in C2:C5, and a third tie-breaker in D2:D5, enter this in…
- Copy Values in Excel: Keep Results, Not FormulasTo copy only the displayed results of cells in Excel, copy the source range, select the destination’s top-left…
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH…
- COUNTIFS Excel Array: Criteria, Formulas, and ExamplesIn Excel for Microsoft 365, COUNTIFS can evaluate an array of criteria and return a separate count for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.