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:
0finds the first exact match. Use it for names, IDs, codes, and unsorted lists.1finds the largest value that is less than or equal to the lookup value. The range must be sorted in ascending order.-1finds 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
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
- Excel Data Analysis Skills: What They Include and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Cell Range Average: Formula and ExamplesTo average a range in Excel, use AVERAGE: if A2:A5 contain 10, 20, a blank cell, and 0,…
- Excel Time Math: Formulas for Adding and Subtracting HoursExcel time math treats times as fractions of a 24-hour day: subtract an earlier time from a later…
- Round Up in Excel: Use ROUNDUP for Any Number or DecimalTo round a value in Excel away from zero, use =ROUNDUP(A2,0) when the number is in cell A2…
- Excel Days Until Date: Formulas, Examples, and FixesTo calculate calendar days until a date in Excel, subtract today’s date from the target date: with the…
- XLOOKUP Syntax Examples for ExcelFor an exact lookup, with the value to find in E2, lookup keys in A2:A100, and results in…
- Excel Sort Dates From Earliest to Latest: A Clear GuideTo sort dates in Excel from earliest to latest, select a cell in your date column, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.