The MATCH function in Excel: how it finds a position

The MATCH function returns the position of a value within a row or column, not the value itself. Its syntax is MATCH(lookup_value, lookup_array, [match_type]): the value to find, the single column or row to search, and how to match. So =MATCH(40, B2:B5, 0) returns 3 when 40 is the third number in B2:B5, the position counted from the top of that range.

On its own, MATCH gives you a number, so it is usually paired with a function that takes a position, most often INDEX. That pairing, the INDEX and MATCH combo, has its own guide. This page stays on MATCH standing alone: how the match_type changes what it finds, how wildcards work, and how XMATCH, the newer Microsoft 365 version, improves on it.

What does the MATCH function do?

MATCH searches a single column or row for a value and reports where it sits, as a number counted from the start of that range. Here a list of products runs down column A, and MATCH finds where one of them lands:

Two things are worth pinning down here. First, the answer is a position, not the matched value: MATCH gives back 3, not Cherries. Second, that position is counted from the top of the array you searched, not the worksheet row number. Cherries sits in worksheet row 4, but it is the third cell of A2:A5, so the position is 3. To turn that number into a value you nest MATCH inside INDEX, which is the whole reason the INDEX and MATCH combo exists.

What does the match_type argument do?

The third argument, match_type, is optional and it is where most MATCH trouble starts. It takes one of three values:

Most of the time you want 0, an exact match:

The trap is that leaving match_type off does not give you an exact match. It gives you 1, an approximate match that expects the range sorted in ascending order and returns the largest value at or below the one you asked for:

That behavior is genuinely useful for banded lookups like tax brackets or grade boundaries, where you want the nearest value at or below the input. The problem is the silent version: reach for MATCH to find an exact ID, forget the 0, and on an unsorted list MATCH can hand back a position that looks right and is not. Write the 0 unless you specifically want the approximate mode. The mirror value, -1, does the same job upside down: it finds the smallest value greater than or equal to the lookup and needs the range sorted descending.

MATCH is also case-insensitive, so it treats a capital and a lowercase letter as the same when matching text.

Matching text with wildcards

When match_type is 0 and the lookup value is text, MATCH accepts two wildcard characters:

An asterisk matches any sequence of characters and a question mark matches exactly one, so wildcards are handy when you know the start of a label but not the rest. To search for an actual question mark or asterisk, type a tilde ahead of it.

What is XMATCH, and how is it different from MATCH?

XMATCH is the modern version of MATCH, and Microsoft points you at it from the MATCH page itself: “Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.” Like MATCH, it returns a position; the difference is in the defaults and the extra control.

The first two arguments are the same as MATCH. The two optional ones are where XMATCH pulls ahead:

• match_mode controls how it matches: 0 exact (the default), -1 exact or the next smaller item, 1 exact or the next larger item, and 2 a wildcard match. The headline change from MATCH is that this defaults to exact, so an XMATCH with no match_mode will not fall back to an approximate match the way an argument-less MATCH does.
• search_mode controls direction: 1 searches first-to-last (the default), -1 searches last-to-first, and 2 and -2 run a binary search that needs the array sorted ascending or descending.

The search_mode of -1 is the one MATCH cannot do at all. It scans from the bottom up, so when a value appears more than once, XMATCH returns the last one instead of the first:

XMATCH is XLOOKUP’s position-finding sibling: XLOOKUP returns the value at a match, XMATCH returns its position, and both share the same match and search modes. The full retrieval story is in the XLOOKUP guide.

On availability, XMATCH follows XLOOKUP exactly. It is in Excel for Microsoft 365, Excel 2021, Excel 2024, and later, and it is not in Excel 2016 or Excel 2019. Open an XMATCH formula in one of those older versions and it shows a #NAME? error, so plain MATCH remains the choice for a workbook that has to run everywhere.

MATCH on its own vs INDEX and MATCH

It helps to keep the two straight. MATCH alone answers where a value is, as a number. The INDEX and MATCH combo takes that number and answers what value sits at that position, by nesting the MATCH inside INDEX. This page owns the standalone function; the combined lookup, including left lookups and multiple criteria, is covered in its own guide.

Why does MATCH return #N/A?

MATCH returns the #N/A error when it cannot find the lookup value, and the cause depends on the match_type.

With an exact match, #N/A means there is no matching entry, usually from a small mismatch: a typo, a stray space, or the number 1005 sitting next to the text 1005, which are not equal to MATCH. Clean the value and make both sides the same type. There is also an approximate-mode case: with match_type 1, a lookup value that falls below every entry in the ascending list returns #N/A, because there is nothing at or under it to settle for. What each error code means, and which ones are safe to ignore, is in Excel’s error values, explained.

The short version

• What it returns: MATCH gives the position of a value within a single column or row, counted from the top of that range, not the value itself. Feed that position to INDEX when you want the value, which is the INDEX and MATCH pattern.
• The match_type: write 0 for an exact match in a range in any order. 1 or an omitted argument is an approximate match that needs the range sorted ascending and returns the largest value at or below the lookup, and -1 is the same in reverse. Omitting it is approximate, not exact, so it is the source of the silent wrong answer.
• Wildcards: with 0 and text, an asterisk matches any run of characters and a question mark matches one; a tilde escapes either.
• XMATCH: the Microsoft 365 and Excel 2021 successor defaults to an exact match and can search bottom-up or by binary search. It is XLOOKUP’s position-finding sibling. A no-match returns #N/A in both.