Excel’s MATCH function has three arguments: MATCH(lookup_value, lookup_array, [match_type]). The first two say what to look for and where to look; the optional third controls whether Excel finds an exact or approximate match. For a typical exact lookup, use match_type 0, as in =MATCH(D2,A2:A10,0).
What MATCH returns
MATCH searches a one-dimensional range or array and returns the position of the matching item within that range. It does not return the matching value itself, nor does it automatically return a worksheet row number. If the match is the fourth item in the selected range, MATCH returns 4, even if that item happens to be on worksheet row 25.
For example, with product names in A2:A6—Apple, Banana, Mango, Orange, and Pear—and Orange entered in D2, =MATCH(D2,A2:A6,0) returns 4. That means Orange is the fourth item in A2:A6. If you use a range beginning on row 2, the result 4 is not row 4; the actual worksheet row is 5.
This positional result makes MATCH useful by itself when you need an item’s location, and especially useful with INDEX, which can use that position to retrieve a value from a corresponding range.
The three MATCH arguments
The arguments must be supplied in this order. The square brackets around match_type in the function’s syntax indicate that it is optional; do not type those brackets into a formula.
- lookup_value: The value Excel should find. It can be a number, text, logical value, cell reference, or a value produced by another formula. For instance, D2 is a convenient reference to a user-entered search term.
- lookup_array: The single row or single column in which Excel searches. It can be a range such as A2:A10 or a horizontal range such as B1:G1. MATCH does not search a two-dimensional table as a whole; select one row or column.
- match_type: A number that specifies the matching behavior: 1, 0, or -1. If omitted, Excel uses 1, so it is safer to specify the argument rather than rely on the default.
In a formula such as =MATCH(D2,A2:A10,0), D2 is lookup_value, A2:A10 is lookup_array, and 0 is match_type. The formula returns a position, not a cell address.
What match_type 0 means
Use 0 when you want an exact match. The values in the lookup range do not need to be sorted, which makes this the most dependable choice for lists of names, product codes, or other identifiers. Excel returns the position of the first matching item it encounters. If there is no exact match, the result is #N/A.
For a concrete example, suppose A2:A6 contains Apple, Banana, Mango, Orange, and Pear, while D2 contains Mango. With that layout, =MATCH(D2,A2:A6,0) returns 3 because Mango is the third item in the selected range. Changing D2 to a value not in the list produces #N/A, rather than a zero or a blank.
With match_type 0, MATCH also recognizes wildcard characters in text searches. An asterisk represents any sequence of characters, and a question mark represents one character. A tilde escapes a wildcard if you want to search for the character itself. For example, with Apple, Apricot, and Banana in A2:A4, =MATCH("Ap*",A2:A4,0) returns 1, the position of the first text that starts with Ap. Because wildcards can match more than one entry, the first matching item is important.
What match_type 1 means
With match_type 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup range must be sorted in ascending order, from smallest to largest. If values are unsorted, the result may be wrong even when the formula does not show an error.
Suppose A2:A6 contains 10, 20, 30, 40, and 50 in ascending order, and D2 contains 34. With that layout, =MATCH(D2,A2:A6,1) returns 3. The third value, 30, is the largest listed value no greater than 34. This behavior is useful for bracket tables, such as assigning a rate or category based on the highest threshold that does not exceed an input.
If D2 is smaller than the first value in an ascending list, there is no eligible value at or below it, so MATCH returns #N/A. A frequent mistake is choosing 1 because it looks like an ordinary match option while the list is unsorted. For names or unsorted records, use 0 instead.
What match_type -1 means
With match_type -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup range must be sorted in descending order, from largest to smallest. This is the reverse ordering requirement from match_type 1.
For example, suppose A2:A6 contains 50, 40, 30, 20, and 10 in descending order, and D2 contains 34. With that layout, =MATCH(D2,A2:A6,-1) returns 2: 40 is the smallest listed value that is at least 34, and it is the second item in the range. If no value is greater than or equal to the lookup value, the result is #N/A.
This option is less common in everyday lookups, but can suit data arranged from highest to lowest. Do not use it on an ascending range or assume that -1 means find the nearest value regardless of direction. The documented sort order and comparison rule determine the result.
Using MATCH with INDEX
MATCH often supplies a position to INDEX. INDEX then returns the value at that position from a related range. This is useful when a lookup key is in one column and the answer is in another, and it avoids MATCH’s limitation of returning only a position.
Suppose product names are in A2:A5, corresponding prices are in B2:B5, and D2 contains Mango. With that layout, =INDEX(B2:B5,MATCH(D2,A2:A5,0)) returns the price from the third position in B2:B5. If B2:B5 contains 0.80, 1.10, 1.25, and 0.95 in that order, the result is 1.25. The MATCH portion returns 3, and INDEX uses 3 to select the third price.
The two ranges need to line up: the first product in A2:A5 must correspond to the first price in B2:B5, and so on. If one range starts on a different row or has a different number of cells, the position may point to the wrong record or produce an error. For a lookup that should return a value from a column to the left of the key column, INDEX and MATCH can also work where a traditional VLOOKUP setup would be awkward.
Common MATCH problems and checks
When a formula gives #N/A or an unexpected position, check the lookup rule and the data before rebuilding the formula. These are frequent causes:
- Wrong match type: For an ordinary exact lookup, use 0. The default is 1, which expects ascending order and performs an approximate match.
- Unsorted approximate-match data: Sort ascending for 1 and descending for -1. If you cannot guarantee the sort order, use exact matching when that suits the task.
- Different underlying data types: A number stored as text may not behave like a numeric value. Check whether the lookup value and list entries are genuinely comparable numbers or text.
- Extra spaces or inconsistent text: A trailing space can make a text value differ from the intended entry. Compare the source values and clean inconsistent data when appropriate.
- Duplicates: Exact MATCH returns the position of the first matching entry, not every matching position. If duplicates exist, confirm that the first occurrence is the one you want.
- Wrong range shape or boundaries: Select one row or one column, and make sure it includes the intended values. Remember that the returned position is counted from the start of that range.
For an exact-match lookup, a practical troubleshooting sequence is to verify that the intended item appears in the selected range, confirm that the formula ends in 0, and check for hidden spaces or number-versus-text differences. If the formula is copied down, consider whether the lookup range should stay fixed. In Microsoft 365 for Windows, adding dollar signs such as A$2:A$10 or $A$2:$A$10 controls which parts of a reference move when copied; choose the reference style that fits the layout.
MATCH compared with XMATCH and XLOOKUP
MATCH remains useful because it is widely recognized and returns a position that can feed INDEX or other calculations. Its main limitation is that its default match type is approximate and its match_type options require attention to sorting. In Microsoft 365 and Excel 2021 or later, XMATCH offers a more flexible alternative and defaults to exact matching. It also provides search-mode choices, including searching from the last item toward the first.
XLOOKUP is another option in Microsoft 365 and Excel 2021 or later when the goal is to return a corresponding value directly rather than calculate a position separately. It can search a lookup range and return a result from another range. These newer functions are not available in every older Excel release, so MATCH with INDEX can be useful when a workbook needs to support older versions. In Excel for the web, function availability can depend on the version and account, while the same basic MATCH argument meanings apply.
Choose based on the job: use MATCH when you specifically need a position, INDEX with MATCH when you want a flexible row-or-column lookup, or XLOOKUP when supported and a direct lookup-and-return formula is clearer. Do not replace MATCH with XMATCH without checking version compatibility if other people will open the workbook in older Excel.
Frequently Asked Questions
What is the correct order of Excel MATCH arguments?
The order is lookup_value, lookup_array, then optional match_type. For an exact lookup, a common pattern is =MATCH(D2,A2:A10,0).
Does MATCH return the row number?
No. It returns the item’s position within the range you supplied. A result of 3 means the third item in that range; it equals a worksheet row number only in certain range layouts.
What happens if I leave match_type out?
Excel uses 1, an approximate match that requires the lookup array to be sorted in ascending order. For most exact lookups, enter 0 explicitly to avoid relying on the default.
Can MATCH find a partial text match?
Yes. With match_type 0, wildcard characters can be used in text lookup values: * matches any sequence of characters and ? matches one character. A tilde can escape a wildcard character.
Why does MATCH return #N/A when the item appears in my list?
Check that the selected range includes the item, that the match type is appropriate, and that the lookup value has the same underlying type and text formatting as the list entry. Extra spaces and numbers stored as text are common sources of mismatches.
Related
- Excel Get Data From a Semantic Model: Connect and Analyze
- Excel Data Model: What It Is and How to Use It
- Excel Data Model: Can You Turn It Off?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
- Excel TRIM Formula Examples for Cleaning Messy TextIn Excel for Microsoft 365, use =TRIM(A2) to remove leading and trailing ordinary spaces from the text in…
- Conditional Formatting Only If a Cell Has DataYes. In Excel for Microsoft 365, select the cells to format, choose Home > Conditional Formatting > New…
- Excel Formula for Extracting Text from a CellTo extract a set number of characters in Excel, use =LEFT(A2,3) when the text is in A2 and…
- Excel Filter Not Showing All Data? Find the Cause FastExcel filters usually miss rows because the filter range stops before the full dataset, or because the values…
- Excel Date Countdown Formula: Calculate Days Left in ExcelIf the target date is in cell B2, enter =B2-TODAY() in another cell to show the number of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.