Excel MATCH Function
last modified October 5, 2026
The MATCH function searches for a specified item in a range of
cells and returns its relative position within that range. It is one of the
foundation lookup functions in Excel and is most often combined with
INDEX to build flexible lookups that work in any direction. This
tutorial covers the syntax and arguments, the three match types, exact and
approximate searches, wildcard matching, sorting requirements, the
MATCH vs XMATCH comparison, and the errors that
commonly trip up users.
MATCH Function Basics
MATCH locates the position of a lookup value within a range.
Unlike VLOOKUP, it returns a position rather than the value itself,
which makes it more flexible for dynamic lookups and for feeding the
row_num or column_num argument of INDEX.
| Component | Description |
|---|---|
| Function Name | MATCH |
| Syntax | =MATCH(lookup_value, lookup_array, [match_type]) |
| Arguments | lookup_value (required), lookup_array (required), match_type (optional) |
| Return Value | Position of the match within lookup_array (1-based) |
This table outlines the MATCH function components. The match_type
argument controls whether MATCH finds an exact or an approximate
match, and its default value is 1.
| Argument | Meaning |
|---|---|
| lookup_value | The value to find. It can be a number, text, logical value, or a cell reference. With match_type 0, text values may contain wildcards. |
| lookup_array | The single row or single column of cells to search. A two-dimensional range returns #N/A. |
| match_type | -1, 0, or 1. Optional; when omitted Excel uses 1. |
The table below summarizes how the three match types behave and which sort
order each one requires. Because the default is 1, an exact lookup must pass
0 explicitly.
| match_type | Behavior | Sort requirement |
|---|---|---|
| 0 | Exact match. Finds the first value equal to lookup_value and supports wildcards. | None |
| 1 or omitted | Largest value less than or equal to lookup_value. | Ascending |
| -1 | Smallest value greater than or equal to lookup_value. | Descending |
Exact Match (0)
Setting match_type to 0 forces an exact match. This is the most common use of
MATCH and works similarly to VLOOKUP's exact match. The lookup
array may be in any order, and MATCH returns the first cell that
matches, not the last.
| A | B |
|---|---|
| Apple | |
| Banana | |
| Cherry | |
| Date | |
| =MATCH("Banana",A1:A4,0) |
The table demonstrates an exact match search for "Banana" in the fruit list. The result will be 2, as Banana is the second item in the range.
=MATCH("Cherry",A1:A4,0) // Returns 3
=MATCH("Grape",A1:A4,0) // Returns #N/A (not found)
=MATCH("banana",A1:A4,0) // Returns 2 (MATCH ignores case)
MATCH does not distinguish uppercase from lowercase letters, so
"banana" finds "Banana". Use IFERROR or IFNA to
handle #N/A results when a value might not exist in the lookup array, for
example =IFNA(MATCH("Grape",A1:A4,0),"not found").
Approximate Match (1)
Match_type 1 finds the largest value less than or equal to the lookup value. The lookup array must be sorted in ascending order.
| A | B |
|---|---|
| 10 | |
| 20 | |
| 30 | |
| 40 | |
| =MATCH(25,A1:A4,1) |
The table shows an approximate match for 25 in a sorted list. The result is 2 because 20 is the largest value ≤ 25.
=MATCH(35,A1:A4,1) // Returns 3 (30 ≤ 35) =MATCH(5,A1:A4,1) // Returns #N/A (below first value) =MATCH(100,A1:A4,1) // Returns 4 (40 ≤ 100)
Approximate matches are useful for grading scales, tax brackets, and other range-based lookups. Always ensure data is sorted ascending for correct results.
Reverse Approximate Match (-1)
Match_type -1 finds the smallest value greater than or equal to the lookup value. The lookup array must be sorted in descending order.
| A | B |
|---|---|
| 40 | |
| 30 | |
| 20 | |
| 10 | |
| =MATCH(25,A1:A4,-1) |
This table shows a reverse approximate match for 25 in a descending list. The result is 2 because 30 is the smallest value greater than or equal to 25.
=MATCH(35,A1:A4,-1) // Returns 1 (40 ≥ 35) =MATCH(5,A1:A4,-1) // Returns 4 (10 ≥ 5) =MATCH(100,A1:A4,-1) // Returns #N/A (above first value)
Reverse matches are less common but useful for certain financial calculations and reverse-sorted data sets. Remember the different sort requirement vs. match_type 1.
MATCH with Wildcards
When match_type is 0, MATCH accepts the wildcard characters
* and ? for partial text matching. This enables
flexible text searches without listing every possible value.
| A | B |
|---|---|
| Apple | |
| Banana | |
| Cherry | |
| Date | |
| =MATCH("B*",A1:A4,0) |
The table demonstrates a wildcard search for text starting with "B". The result is 2 (Banana). Wildcards only work with text values.
=MATCH("*rry",A1:A4,0) // Returns 3 (Cherry)
=MATCH("?ate",A1:A4,0) // Returns 4 (Date)
=MATCH("~*",A1:A4,0) // Returns #N/A (no literal asterisk)
The asterisk (*) matches any sequence of characters, while the
question mark (?) matches any single character. To search for a
literal asterisk or question mark, precede it with a tilde (~), as
in "~*". Wildcards work only with exact matching and only with
text values; they are ignored when match_type is 1 or -1.
Sorting Requirements
The sort order of lookup_array is the single most common source of
wrong results with MATCH. The rule depends on match_type.
| match_type | Required order | If the order is wrong |
|---|---|---|
| 0 | Any order | No effect; the result is still correct |
| 1 or omitted | Ascending (smallest to largest) | A wrong or meaningless position is returned |
| -1 | Descending (largest to smallest) | A wrong or meaningless position is returned |
Approximate matching uses a fast binary search, which assumes the data is
already ordered. If it is not, MATCH can stop early and return a
position that looks valid but points at the wrong row. Excel does not raise an
error in that case, so the mistake is easy to miss. Always sort the array first
or switch to match_type 0 for an exact match.
INDEX-MATCH Combination
MATCH is often paired with INDEX to create more
flexible lookups than VLOOKUP. This combination can look left and
handle dynamic column references.
| A | B | C |
|---|---|---|
| Apple | Red | |
| Banana | Yellow | |
| Cherry | Red | |
| =INDEX(B1:B3,MATCH("Banana",A1:A3,0)) |
The table shows INDEX-MATCH finding the color of "Banana". MATCH locates the row (2), and INDEX returns the corresponding color from column B ("Yellow").
=INDEX(B1:B10,MATCH(D1,A1:A10,0)) // Basic lookup =INDEX(A1:Z100,MATCH(D1,A1:A100,0),MATCH(E1,A1:Z1,0)) // 2-way lookup =INDEX(B1:B10,MATCH(1,(A1:A10=D1)*(C1:C10=D2),0)) // Multi-criteria
INDEX-MATCH is more flexible than VLOOKUP because it
can look left, right, or anywhere in the sheet. The last example shows array
formula syntax for multiple criteria (entered with Ctrl+Shift+Enter in older
Excel).
MATCH with Dynamic Ranges
MATCH works well with dynamic named ranges and tables. This example uses MATCH to find the last non-empty cell in a column.
| A | B |
|---|---|
| Data1 | |
| Data2 | |
| Data3 | |
| =MATCH(REPT("z",255),A:A) |
The table demonstrates finding the last text entry in column A. The formula
returns 3, the position of "Data3". REPT creates a text value
guaranteed to be "larger" than any real data.
=MATCH(9.9E+307,A:A) // Last number in column =MATCH(2,1/(A1:A100<>"")) // Last non-empty (array formula) =MATCH(TRUE,INDEX(ISBLANK(A:A),0),0)-1 // Last non-blank
These techniques help create dynamic ranges that automatically adjust as data changes. The second example is an array formula that finds the last non-empty cell regardless of data type.
Errors and Troubleshooting
MATCH can return errors or, more dangerously, a wrong position with
no warning. The table below lists the most common causes together with their
symptoms and fixes.
| Cause | Symptom | Fix |
|---|---|---|
| Lookup value does not exist | #N/A error | Check spelling and data types, or wrap the formula in IFNA |
| match_type omitted | Unexpected value from an approximate match | Pass 0 explicitly for an exact match |
| Approximate match on unsorted data | Wrong position returned, with no error | Sort the array ascending (or descending for -1), or use 0 |
| Lookup value below the smallest item | #N/A with match_type 1 | Ensure the value is within the array range, or handle with IFNA |
| Numbers stored as text | #N/A even though the value looks present | Make both sides the same type, for example with VALUE |
| Searching for a literal * or ? | An unintended match | Escape the character with a tilde, for example "~*" |
| lookup_array is two-dimensional | #N/A error | Pass a single row or single column of cells |
| Different letter case expected | The match succeeds anyway | MATCH is case-insensitive; use EXACT for a case-sensitive test |
A common defensive pattern is =IFNA(MATCH(D1,A1:A10,0),"not found"),
which replaces the raw error with a readable message.
MATCH vs. XMATCH
XMATCH is the modern replacement for MATCH. It returns
a position as well, but it is easier to use safely because it performs an exact
match by default and can search in either direction.
| Feature | MATCH | XMATCH |
|---|---|---|
| Default match | Approximate (1) | Exact (0) |
| Search direction | First to last | Configurable, including first and last |
| Return value | Position | Position |
| Wildcards | Only with match_type 0 | Supported, selectable |
| Not-found handling | #N/A, wrap in IFNA | if_not_found argument |
| Availability | All versions | Excel 2021 and 365 |
XMATCH is the better choice when it is available, but
MATCH still works in every version of Excel, including files that
must remain compatible with older releases. The two functions return the same
kind of position, so a MATCH formula can often be swapped for
XMATCH without other changes.
The MATCH function is a small but essential building block of
Excel lookups. It never returns a value, only the position of a match, and that
single fact is what makes it so useful: the position can drive
INDEX, feed a dynamic reference, or count where something sits in
a list. Remember the three rules that prevent almost every mistake. Pass
0 for exact matches, sort the array ascending for
1 and descending for -1, and treat a wrong position
on unsorted data as a silent failure rather than a correct answer. Master those
habits, and MATCH will make your spreadsheets more robust and your
lookups far more flexible.
Author
List all Excel tutorials.