MATCH

What it does

MATCH(lookup_value, lookup_array, [match_type])

MATCH returns a position, not a value. Given a lookup value v and a one-dimensional lookup array L with entries L[1] … L[n], it returns an index i ∈ 1..n chosen by the match_type rule, or #N/A.

The three rules, as Microsoft documents them:

Two of the three rules therefore carry a caller obligation that Excel does not check. The 1 and -1 rules are ordered searches: they are only meaningful over a sorted array, and Excel raises no error when the array is not sorted — it returns a deterministic index computed under a false premise.

Microsoft's documentation also states the sort order it means, which is unusually explicit and worth quoting structurally rather than paraphrasing: ascending order is given as -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE. That is a cross-type total order: all numbers precede all text, text precedes FALSE, and FALSE precedes TRUE. This is the ordering an ordered MATCH compares against when the array mixes types, and it is the same ordering that governs the older approximate lookups.

The index returned is relative to L, not to the worksheet. MATCH(v, A5:A9, 0) returning 2 means A6 — the caller adds the offset. That is why MATCH is almost always seen inside INDEX, which does the arithmetic.

Arguments

lookup_value — the value to find. May be a number, text, a logical, or a reference to one. In exact mode (match_type 0) a text lookup value may contain wildcards: ? matches any single character, * matches any run of characters, and ~ escapes a literal ?, * or ~. Wildcards are documented only for match_type 0.

lookup_array — the sequence to search. Conventionally a single row or single column. What MATCH does with a genuinely two-dimensional array is not stated in the documentation and is not asserted here.

match_type — optional, defaults to 1. This default deserves the same suspicion as VLOOKUP's omitted fourth argument, for the same reason: omitting it selects an ordered search whose failure on unsorted data is a plausible wrong index rather than an error. A reader who writes MATCH(v, L) meaning "where is v" has written "where is the last entry not exceeding v, assuming L is sorted ascending". Pass 0 explicitly when you mean exact.

Try it

Quirks

Result and edge cases

The return kind is a number: a 1-based index into lookup_array.

• Case-insensitivity. Microsoft states plainly that MATCH does not distinguish uppercase from lowercase. Exact mode is therefore not string identity.
• Errors, empty cells and omitted arguments follow the shared rules in coercion and lifting.

Errors

Documented: #N/A when no match is found. Under match_type 1 that includes the case where v is smaller than every entry; under -1, where v is larger than every entry.

How to implement

Notes for implementers

• Three match_type values are two different algorithms: an unordered scan for 0, and an ordered search for ±1. Sharing one code path between them is how implementations acquire the wrong behaviour on unsorted input.
• Exact mode is case-insensitive but numerically strict. Those two facts pull in opposite directions and are easy to implement inconsistently.
• Wildcard handling belongs to exact mode only, and ~ escaping has to be handled before pattern compilation, not after.
• lookup_value must admit arrays and spill elementwise.

Rust code

eager(
    MATCH,
    "MATCH",
    CallableArityShape::range(2, 3),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("lookup_array"),
        Parameter::value("match_type"),
    ],
    match_,
)

/// MATCH's walk for match_type -1: see the module documentation.
fn walk_descending(
    site: &Site<'_>,
    services: &mut EvalServices<'_>,
    line: Line<'_>,
    key: Item<'_>,
) -> Step<Option<u64>> {
    let mut best = None;
    line.scan(site, services, &mut |at, value| {
        let cell = item(site, 1, value)?;
        if cell.rank() != key.rank() {
            return Ok(ControlFlow::Continue(()));
        }
        Ok(match order(site, cell, key)? {
            Ordering::Equal => {
                best = Some(at);
                ControlFlow::Break(())
            }
            Ordering::Greater => {
                best = Some(at);
                ControlFlow::Continue(())
            }
            Ordering::Less => ControlFlow::Break(()),
        })
    })?;
    // An equal cell ends the walk as the result; a smaller one ends it with the last larger.
    Ok(best)
}

/// MATCH(lookup_value, lookup_array, [match_type]): the 1-based position of the lookup value in a
/// row or a column. match_type is read as a number and only its sign counts: 0 an exact match
/// (with wildcards), positive (the default) the bisection, negative the descending walk; an
/// omitted or blank match_type is 0. A lookup array that is not one row or one column, a
/// reference of several areas, and a lookup array that is a single value and not a reference are
/// #N/A, as is no match.
fn match_(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(MATCH, services);
    Site::finish(matched(&site, arguments, services))
}

fn matched(
    site: &Site<'_>,
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    let key = lookup_key(site, &arguments[0], BlankKey::Zero)?;
    let grid = match shape(&arguments[1], 1) {
        Shape::Error(code) => return Err(Exit::Error(code)),
        Shape::Grid(grid) => Some(grid),
        Shape::Areas | Shape::Blank => None,
    };
    let sign = match arguments.get(2) {
        None => 1.0,
        Some(value) if value.is_missing() || value.is_empty() => 0.0,
        Some(value) => index_number(site, 2, value)?,
    };
    text_limit(key)?;
    let not_found = Exit::Error(WorksheetErrorCode::NA);
    let line = match grid {
        // A single value given directly is not searched.
        Some(Grid::One(_)) | None => return Err(not_found),
        Some(grid) => grid.line().ok_or(not_found)?,
    };
    let found = if sign == 0.0 {
        first_hit(site, services, line, &Target::new(key, true))?
    } else if sign > 0.0 {
        bisect(site, &line.held(site, services)?, key)?
    } else {
        walk_descending(site, services, line, key)?
    };
    match found {
        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. XMATCH keeps the position-returning shape but splits the single match_type flag into an explicit match_mode and search_mode, defaults to exact match rather than ordered match, and adds reverse and binary search. For new work XMATCH is the better choice. Microsoft has not deprecated MATCH; it remains supported and remains what existing workbooks contain.
• INDEX is MATCH's usual partner: INDEX(R, MATCH(v, L, 0)) is the classic exact-lookup composition, and it is what XLOOKUP packages into one call.
• LOOKUP shares the ordered-search rule with match_type 1 and has no exact mode.
• Readers confuse MATCH with FIND and SEARCH (which locate a substring inside one text value, not a value inside a list).

Named variants

No named variants are recorded yet.