XMATCH

What it does

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

XMATCH returns the 1-based position of a value within a range or array. Given a lookup value v and a lookup array L of n entries, it selects an index i ∈ 1..n and returns i, or #N/A.

What distinguishes it from MATCH is that the single ternary match_type flag is split into two orthogonal arguments:

• match_mode — the predicate. Exact (default), exact-or-next-smallest, exact-or-next-largest, or wildcard.
• search_mode — the traversal. First-to-last (default), last-to-first, binary ascending, or binary descending.

That split is not cosmetic. In MATCH, choosing "nearest below" also chose "assume ascending sort" and (in practice) a particular traversal; the three were welded together. In XMATCH you can ask for an exact match searched backwards — which is how you find the last occurrence of a repeated key, something MATCH cannot express at all. You can also ask for a nearest-below match over a linearly scanned array, without committing to the sorted-array premise that binary search requires.

The default combination is exact match, first-to-last. Its failure mode is #N/A: visible. That is a deliberate reversal of MATCH's default, whose failure mode is a plausible wrong index.

Arguments

Microsoft documents XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]).

lookup_value — the value to locate.

lookup_array — the array or range to search.

match_mode — optional, default 0:

search_mode — optional, default 1:

The position most often misused is the third. XMATCH(v, L, -1) is not a reverse search — it is "exact or next smallest, searched forwards". A reverse search is XMATCH(v, L, 0, -1). Both spellings contain a -1 and neither is a type error, so the mistake survives entry and shows up as a subtly wrong index.

Note also that match_mode ±1 does not by itself require a sorted array — only search_mode ±2 does. Nearest-below over an unsorted array is a legal, meaningful request under linear traversal. This is a genuine expressive gain over MATCH and is easy to overlook.

Try it

Quirks

Result and edge cases

The return kind is a number: an index relative to lookup_array, not to the worksheet.

• Exact numeric comparison is strict. BUG-FUNC-004, signed off against live Excel 16.0 build 20026 on 2026-06-20, records =XMATCH(0.1+0.2,{0.3},0) → #N/A, deliberately contrasted with the tolerant comparison families where =0.1+0.2=0.3 is TRUE.
• Binary search on unsorted input returns something rather than erroring. Microsoft states only that results are invalid; the behaviour is therefore undefined by documentation, not defined-and-bad.
• Errors, blanks and omitted arguments otherwise follow coercion and lifting.

Errors

Documented: no error is named beyond the ordinary #N/A for a value not found, and a warning that binary search over an unsorted array yields invalid results — wrong answers, not an error value.

Error values arriving in lookup_value propagate. What an error inside lookup_array does is not documented; BUG-FUNC-040 names the error-cell lane as an explicitly separate, open probe, which is a fair signal that nobody has pinned it.

How to implement

Notes for implementers

• match_mode and search_mode are independent inputs to one search, and the implementation has to decide what each of the sixteen combinations means. Wildcard matching under binary search is the combination with no documented meaning; it should be a recorded decision, not an accident of code structure.
• Reverse exact search must return the last matching index, which means it cannot be implemented as forward search over a reversed array without mapping the index back.
• The same index-mapping obligation applies to blank skipping under binary search: the search runs over the comparable subsequence, and the chosen position must be translated back to the original 1-based index. BUG-FUNC-040 records exactly this repair.
• Exact mode is numerically strict while much of Excel's comparison surface is tolerant; the two comparison helpers must not be shared.

Rust code

eager(
    XMATCH,
    "XMATCH",
    CallableArityShape::range(2, 4),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("lookup_array"),
        Parameter::value("match_mode"),
        Parameter::value("search_mode"),
    ],
    xmatch,
)

/// XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]): the 1-based position, by the
/// modes the module documentation describes. A lookup array of more than one row and column is
/// #VALUE!, a reference of several areas #N/A; a single value is a line of one cell. A blank or
/// omitted lookup array is #VALUE! in its argument's turn, before the modes are read.
fn xmatch(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(XMATCH, services);
    Site::finish(xmatched(&site, arguments, services))
}

fn xmatched(
    site: &Site<'_>,
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    let key = lookup_key(site, &arguments[0], BlankKey::Blank)?;
    let grid = match shape(&arguments[1], 1) {
        Shape::Error(code) => return Err(Exit::Error(code)),
        Shape::Areas => None,
        Shape::Blank => return Err(Exit::Error(WorksheetErrorCode::Value)),
        Shape::Grid(grid) => Some(grid),
    };
    let (mode, search) = modes(
        site,
        arguments.get(2).map(|value| (2, value)),
        arguments.get(3).map(|value| (3, value)),
    )?;
    let Some(grid) = grid else {
        return Err(Exit::Error(WorksheetErrorCode::NA));
    };
    let line = grid.line().ok_or(Exit::Error(WorksheetErrorCode::Value))?;
    match xsearch(site, services, line, key, mode, search)? {
        Some(at) => position(site, at),
        None => Err(Exit::Error(WorksheetErrorCode::NA)),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• XMATCH supersedes MATCH. Same job, orthogonal controls, exact-match default, plus reverse and binary search. Microsoft has not deprecated MATCH, which remains supported.
• XLOOKUP is XMATCH plus the return step, sharing the match_mode and search_mode vocabulary exactly. INDEX(R, XMATCH(v, L, m, s)) and XLOOKUP(v, L, R, , m, s) are the same query written two ways; the difference is that XLOOKUP also carries if_not_found.
• INDEX is the usual consumer of XMATCH's result.
• VLOOKUP/HLOOKUP contain a MATCH-shaped search internally; XMATCH is the modern standalone form of that search.

Named variants

No named variants are recorded yet.