XLOOKUP

What it does

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP finds a position in one vector and returns the corresponding slice of another. Given a lookup value v, a lookup array L of n entries, and a return array R, it determines an index i ∈ 1..n by the match rule below and returns R's i-th slice — a single value when R is a vector, and a whole row or column when R is a two-dimensional area aligned to L.

The separation is the design point. In VLOOKUP the returned column is named by a positional integer counted inside the searched table, so the search and the return are coupled through a shared geometry. In XLOOKUP they are two independent arguments: R may sit to the left of L, may be wider or taller, and may live in a different part of the sheet, as long as its length along the matching axis equals L's.

The match rule is a two-dimensional choice, and both dimensions are explicit:

• match_mode decides what counts as a match: exact only (default), exact-or-next- smaller, exact-or-next-larger, or wildcard.
• search_mode decides how the vector is traversed: forward from the first item, backward from the last, or by binary search assuming L is sorted ascending or descending.

Making both explicit is the substantive improvement over the older lookups, where the two were entangled in one flag and the default silently chose the risky combination. XLOOKUP's default is exact match, forward search — the combination whose failure mode is a visible #N/A rather than a plausible wrong number.

Arguments

Microsoft documents the signature as XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

lookup_array — the array or range to search. Its length along the matching axis fixes the correspondence with return_array.

return_array — the array or range to return from. Aligned to lookup_array by position, not by address.

if_not_found — optional. The value to return when no match is found. When it is absent, a miss is #N/A. This argument is the reason IFNA(XLOOKUP(...), ...) is usually redundant.

match_mode — optional, default 0:

search_mode — optional, default 1:

The two argument positions most commonly misread are match_mode and search_mode themselves — they are easy to swap, and both accept small signed integers, so a swap is not a type error. XLOOKUP(v, L, R, , -1) is "next smaller item, forward search"; XLOOKUP(v, L, R, , , -1) is "exact match, searched backwards". They are different functions, and both are spelled with a -1.

Note also the fourth position: if_not_found sits between the return array and the mode flags. Writing XLOOKUP(v, L, R, 0) intending exact match sets the not-found value to 0 and leaves the mode at its default — a mistake that produces 0 instead of #N/A on a miss.

Try it

Quirks

Result and edge cases

XLOOKUP returns whatever return_array holds at the matched position: a scalar for a vector return array, or an entire row or column for a two-dimensional one. The row-or-column return is what makes XLOOKUP usable as a record fetch — one call, whole record — where VLOOKUP needs one call per field.

• Binary search modes on unsorted data. Microsoft states only that binary search "relies on" the array being sorted. As with VLOOKUP's approximate mode, that is a precondition rather than a checked constraint: unsorted input under search_mode ±2 yields a deterministic but undocumented answer, not an error.
• Empty cells, omitted arguments, and error inputs follow the shared rules in coercion and lifting; the reference-aware argument preparation XLOOKUP carries is described in the call pipeline.

Errors

Documented by Microsoft: #N/A when no match exists and no if_not_found was supplied. Microsoft additionally warns that binary search modes over an unsorted array produce invalid results — note that this is a warning about wrong answers, not about an error value.

XLOOKUP is also commonly reported to return #VALUE! when lookup_array and return_array have incompatible dimensions, and #REF! for arrays in different workbooks in some configurations. The Handbook has not verified either against the current documentation page or against a live build, and does not assert them.

How to implement

Notes for implementers

• The four match_mode values are not four settings of one comparison; wildcard mode changes the predicate from equality to pattern matching, and the two "next item" modes require an order over values that exact mode does not need.
• search_mode and match_mode interact. Binary search is only coherent with an ordered predicate; what binary search does under wildcard mode is a real question, not a don't-care.
• Blank-skipping belongs to the ordered search paths. An implementation that treats a blank as an incomparable value and aborts will fail on ordinary trailing-blank ranges.
• lookup_value must admit arrays, and return_array must be able to yield a whole row or column per match. Those two together mean the natural result shape is not always a vector.
• if_not_found is returned verbatim; it is not coerced to the return array's type, and it can itself be an array.

Rust code

eager(
    XLOOKUP,
    "XLOOKUP",
    CallableArityShape::range(3, 6),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("lookup_array"),
        Parameter::value_or_reference("return_array"),
        Parameter::value_or_reference("if_not_found"),
        Parameter::value("match_mode"),
        Parameter::value("search_mode"),
    ],
    xlookup,
)

/// XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],
/// [search_mode]): XMATCH's search of lookup_array, and the matching row (a vertical lookup
/// array) or column (a horizontal one) of return_array, which must have as many rows or columns:
/// a reference into return_array when it is one, else the array's part. With no match the result
/// is if_not_found as it is, or #N/A when it is omitted. A lookup array of one cell selects the
/// whole of a return array of one row, else of one column; a return array of more rows and columns
/// is #VALUE! for it. A blank or omitted lookup or return array is #VALUE! in its argument's turn,
/// before the modes are read; the shape rules apply after them.
fn xlookup(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(XLOOKUP, services);
    Site::finish(xlooked_up(&site, arguments, services))
}

fn xlooked_up(
    site: &Site<'_>,
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Step<CalcValue> {
    let key = lookup_key(site, &arguments[0], BlankKey::Blank)?;
    let lookup_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 return_grid = match shape(&arguments[2], 2) {
        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(4).map(|value| (4, value)),
        arguments.get(5).map(|value| (5, value)),
    )?;
    let (Some(lookup_grid), Some(return_grid)) = (lookup_grid, return_grid) else {
        return Err(Exit::Error(WorksheetErrorCode::NA));
    };
    let value_error = Exit::Error(WorksheetErrorCode::Value);
    let line = lookup_grid
        .line()
        .ok_or(Exit::Error(WorksheetErrorCode::Value))?;
    let (rows, columns) = (lookup_grid.rows(), lookup_grid.columns());
    // Whether the result is a row of the return array (true) or a column.
    let by_row = if rows == 1 && columns == 1 {
        if return_grid.rows() == 1 {
            true
        } else if return_grid.columns() == 1 {
            false
        } else {
            return Err(value_error);
        }
    } else if columns == 1 {
        if return_grid.rows() != rows {
            return Err(value_error);
        }
        true
    } else {
        if return_grid.columns() != columns {
            return Err(value_error);
        }
        false
    };
    let Some(at) = xsearch(site, services, line, key, mode, search)? else {
        return match arguments.get(3) {
            Some(fallback) if !fallback.is_missing() => Ok(fallback.clone()),
            _ => Err(Exit::Error(WorksheetErrorCode::NA)),
        };
    };
    Ok(part(&arguments[2], return_grid, by_row, at))
}

/// Row (`by_row`) or column `at` (0-based) of a return grid: a reference into its area, or the
/// array's part, or the single value.
fn part(original: &CalcValue, grid: Grid<'_>, by_row: bool, at: u64) -> CalcValue {
    match grid {
        Grid::Area { area, .. } => {
            let first = area.first();
            let last = area.last();
            let selected = if by_row {
                let row = first.row() + at as u32;
                span(area, (row, first.column()), (row, last.column()))
            } else {
                let column = first.column() + at as u32;
                span(area, (first.row(), column), (last.row(), column))
            };
            CalcValue::reference(ReferenceLike::area(selected))
        }
        Grid::Array(array) => {
            let shape = array.shape();
            let cells: Vec<CalcValue> = if by_row {
                array
                    .row_slice(at as usize)
                    .expect("a row of the array")
                    .to_vec()
            } else {
                (0..shape.rows)
                    .map(|row| {
                        array
                            .get(row, at as usize)
                            .expect("a cell of the array")
                            .clone()
                    })
                    .collect()
            };
            if cells.len() == 1 {
                return cells.into_iter().next().expect("one cell");
            }
            let shape = if by_row {
                ArrayShape {
                    rows: 1,
                    cols: cells.len(),
                }
            } else {
                ArrayShape {
                    rows: cells.len(),
                    cols: 1,
                }
            };
            CalcValue::array(CalcArray::new(shape, cells).expect("a row or column"))
        }
        Grid::One(_) => original.clone(),
    }
}

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 supersedes VLOOKUP, HLOOKUP and LOOKUP. It covers all three, in both orientations, with an exact-match default, an explicit not-found value, backward search, and a return array that need not lie to the right of (or below) the search vector. Microsoft has not deprecated the older functions: they remain supported, and existing workbooks keep working. For new work XLOOKUP is the better default.
• XMATCH is XLOOKUP without the return step: same match_mode and search_mode vocabulary, returning the position instead of the value. INDEX(R, XMATCH(v, L, ...)) is XLOOKUP decomposed.
• FILTER answers a different question: all rows satisfying a predicate, not the one row matching a key.
• Readers coming from INDEX/MATCH should note that XLOOKUP's alignment is positional along the matching axis, exactly as INDEX/MATCH's is — the relationship is a repackaging, not a new mechanism.

Named variants

No named variants are recorded yet.