LOOKUP

What it does

LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP finds, among a sequence of keys, the largest key that is less than or equal to the value you are looking for, and returns the value aligned with it. It has no exact-match mode and no not-found argument: approximate is the only behaviour, and #N/A is the only miss.

Microsoft documents the requirement that makes it work: the keys must be in ascending order. LOOKUP does not verify this. On unsorted data it returns a value — some value — without complaint. Everything difficult about this function traces back to that one unchecked precondition.

The pinned catalogue displays the vector form, LOOKUP(lookup_value, lookup_vector, [result_vector]), and accepts two or three arguments. The array form below shares that name but assigns a different role to its second argument.

Arguments

The arity is 2 to 3, which is what makes the two forms indistinguishable from the signature alone: a two-argument call is the array form or a vector form without result_vector, and only the second argument's shape tells you which.

The position readers misjudge most is result_vector's alignment. It is positional, not geometric: the nth element of result_vector pairs with the nth element of lookup_vector, in each vector's own reading order. A row paired with a column works; a five-element key vector paired with a six-element result vector does not fail loudly.

Try it

Quirks

The two forms

Microsoft's documentation presents LOOKUP as two forms sharing a name, distinguished by whether the second argument is a vector or a rectangular array.

Vector form — LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_vector is a single row or a single column of keys, in ascending order. The search finds the largest key not exceeding lookup_value, and the answer is the correspondingly positioned element of result_vector. Microsoft documents that lookup_vector and result_vector must be the same size; where they are not, the behaviour is not something to rely on.

The two vectors need not be parallel in orientation or adjacent in the sheet — they can be a row and a column, or live on different sheets. Alignment is by position, not by geometry, which is what makes the vector form more flexible than HLOOKUP and VLOOKUP and also what makes a size mismatch so easy to introduce.

Omitting result_vector returns the matched key itself, from lookup_vector.

Array form — LOOKUP(lookup_value, array)

One rectangular argument serves as both keys and results. Microsoft documents the axis rule: if array has more columns than rows, the search runs along the first row and the result comes from the last row; otherwise — square, or taller than wide — the search runs along the first column and the result comes from the last column.

That rule is shape-driven and therefore silent: an array that changes from wide to tall as data is added changes which axis is searched, with no error and no visible cause. Microsoft's own documentation says the array form is provided for compatibility with other spreadsheet programs and recommends HLOOKUP or VLOOKUP instead. The Handbook agrees, and would put it more strongly: the array form's shape rule is a trap, and there is no case where it is the clearest way to express an intention.

Result and edge cases

Return kind: whatever the aligned element holds — number, text, logical, or error.

LOOKUP is reference-aware (arg_preparation_profile: RefsVisibleInAdapter), so its vectors arrive as live references and need not be materialized whole to be searched.

• lookup_value below every key — #N/A. This is the documented miss, and it is the only miss: a value above every key matches the last one, because the rule is "largest key not exceeding".
• Duplicate keys — which of several equal keys is selected is a consequence of the search strategy, and is not established here.
• Mixed types among the keys. "Largest not exceeding" needs a total order across numbers, text and logicals. Excel has one; where its type boundaries fall, and what that means for a mixed key vector, is not established here.
• Empty cells among the keys — skipped, treated as a key, or treated as zero — is a real question for partially filled vectors, and the answer interacts with the sortedness assumption.
• Errors among the keys. This case deserves its own paragraph, below.
• Text keys compare case-insensitively under Excel's usual convention, and the collation governing their order may be locale-dependent. Not established here.

The LOOKUP(2, 1/(condition), result) idiom. A widely used construction relies on LOOKUP searching a vector that is deliberately full of #DIV/0! errors with a search value that exceeds every non-error entry, to retrieve the last row satisfying a condition. Its mechanism requires that errors be passed over rather than propagated, and that an out-of-range search value settle on the last valid key even though the vector is not sorted. Microsoft's documentation does not describe this behaviour, and the Handbook states plainly: this idiom is folklore that works, its mechanism is not documented, and nothing in the Handbook has verified it. It is the single most interesting open question on this page, because the idiom is load bearing in real workbooks and rests on unspecified behaviour.

Errors

Microsoft's page documents #N/A when lookup_value is smaller than the smallest key.

Beyond that the documented error surface is thin, and thin for a reason: LOOKUP's failure mode is usually not an error at all. Unsorted keys, mismatched vector sizes, and the array form's shape rule all produce values. A function that returns the wrong number quietly is worse than one that errors, and this is the function's defining characteristic.

An error value supplied as lookup_value propagates under the universal rule in coercion and lifting. Errors inside the key vector are the open question described above.

How to implement

Notes for implementers

• The search strategy is observable. Because Excel does not validate sortedness, whatever the implementation does on unsorted data is the specification. A binary search and a linear scan agree on sorted input and disagree on unsorted input, so the choice is not free and cannot be made on performance grounds alone. Matching Excel here requires evidence, not reasoning.
• The array form's axis rule uses >, not >=. Wide means more columns than rows; square falls to the column branch. An implementation that writes the comparison the other way is wrong only for square arrays, which is exactly the case a hand-written test is least likely to include.
• Size mismatch between the vectors needs a declared policy, since the documentation states a requirement rather than a behaviour. Silently truncating, silently extending, and erroring are all defensible and all different.
• Errors in the key vector need a declared policy too, and it is not the ordinary propagation rule if the well-known idiom works as reported.
• Reference-awareness is a performance property here. Searching a whole-column key vector should not materialize a million cells; the shared model records selective dereference as a function-local capability rather than a general pipeline one.
• Do not "improve" LOOKUP. Rejecting unsorted input, or checking vector sizes, would make a better function and a worse LOOKUP — workbooks that Excel computes would stop computing.

Rust code

eager(
    LOOKUP,
    "LOOKUP",
    CallableArityShape::range(2, 3),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("lookup_vector"),
        Parameter::value_or_reference("result_vector"),
    ],
    lookup,
)

/// LOOKUP(lookup_value, lookup_vector, [result_vector]): the bisection of the lookup line, and
/// the value at the found position of the result line. With two arguments a grid wider than it is
/// tall is searched in its first row and answered from its last row, any other in its first
/// column and answered from its last column; a third argument gives the result line, which must be
/// one row or one column (a single cell runs along its row). A result line shorter than the found
/// position is #N/A for an array; for a reference Excel reads past it, which is not modelled. A
/// lookup or result reference of several areas, and no match, are #N/A; a blank or omitted lookup
/// or result vector is #VALUE! in its argument's turn. A blank result cell is Empty.
fn lookup(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(LOOKUP, services);
    Site::finish(looked_up(&site, arguments, services))
}

fn looked_up(
    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::Areas => None,
        Shape::Blank => return Err(Exit::Error(WorksheetErrorCode::Value)),
        Shape::Grid(grid) => Some(grid),
    };
    let result = match arguments.get(2).map(|value| shape(value, 2)) {
        None => None,
        Some(Shape::Error(code)) => return Err(Exit::Error(code)),
        Some(Shape::Areas) => return Err(Exit::Error(WorksheetErrorCode::NA)),
        Some(Shape::Blank) => return Err(Exit::Error(WorksheetErrorCode::Value)),
        Some(Shape::Grid(result)) => Some(result),
    };
    text_limit(key)?;
    let Some(grid) = grid else {
        return Err(Exit::Error(WorksheetErrorCode::NA));
    };
    let wide = grid.columns() > grid.rows();
    let line = if wide {
        grid.row_line(0)
    } else {
        grid.column_line(0)
    };
    let Some(at) = bisect(site, &line.held(site, services)?, key)? else {
        return Err(Exit::Error(WorksheetErrorCode::NA));
    };
    let Some(result) = result else {
        return if wide {
            grid.value(site, services, grid.rows() - 1, at)
        } else {
            grid.value(site, services, at, grid.columns() - 1)
        };
    };
    let (rows, columns) = (result.rows(), result.columns());
    let along_row = if rows == 1 {
        true
    } else if columns == 1 {
        false
    } else {
        return Err(Exit::Error(WorksheetErrorCode::NA));
    };
    let length = if along_row { columns } else { rows };
    if at >= length {
        return match result {
            // Excel reads the cells past the reference given, which the engine cannot see as
            // prerequisites of the formula: not modelled.
            Grid::Area { .. } => Err(site.not_modelled(Some(2))),
            Grid::Array(_) | Grid::One(_) => Err(Exit::Error(WorksheetErrorCode::NA)),
        };
    }
    if along_row {
        result.value(site, services, 0, at)
    } else {
        result.value(site, services, at, 0)
    }
}

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

• XLOOKUP is the modern replacement and supersedes every use of LOOKUP: it defaults to exact match, takes an explicit not-found value, searches in either direction, supports a reverse search mode, and separates the search array from the return array — which is the vector form's flexibility with the sizes checked. New code should use it. Microsoft retains LOOKUP for workbook compatibility.
• VLOOKUP / HLOOKUP are the better-specified relatives, and are what Microsoft's own page recommends in place of LOOKUP's array form. They also offer an exact-match mode, which LOOKUP does not.
• MATCH with match_type 1 implements the same "largest not exceeding" rule and returns the position instead of the value; INDEX/MATCH is the decomposed, checkable version of what LOOKUP does in one call.
• XMATCH adds a binary-search mode that makes the sortedness assumption explicit rather than implicit — arguably the honest version of LOOKUP's contract.
• Module siblings. The reference implementation places LOOKUP with FREQUENCY, PROB and MODE.MULT. That grouping is about shared ordered-bin machinery, not about Excel's categories — FREQUENCY's bin assignment is the same "largest not exceeding" question asked of a whole array at once.

Named variants

No named variants are recorded yet.