VLOOKUP

What it does

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• a lookup value v,
• a table T with rows 1..r and columns 1..c,
• a column index k,
• and a mode flag,

VLOOKUP searches only the first column of T — T[1,1] … T[r,1] — for a row index i, then returns T[i,k]. Two things about that sentence carry most of the function's real-world failure surface, and both are structural rather than numerical:

1. The search column is fixed at column 1 of T. It is not a separate argument. The caller chooses it implicitly by where the range starts, which is why inserting a column to the left of a table changes what VLOOKUP searches.
2. k is an absolute index within T, not an offset from the search column. k = 1 returns the search column itself; k = 2 returns the column immediately to its right. k is counted from T's own left edge, so it is invalidated by any column inserted inside T — the formula keeps working and silently returns a different column.

The choice of i depends on the mode flag, and the two modes are genuinely different algorithms rather than two settings of one algorithm:

• Exact mode (range_lookup = FALSE). i is the smallest row index whose first-column value equals v. Equality is Excel's worksheet equality — case-insensitive for text, and, for numbers, the tolerant near-equality Excel applies in comparison contexts rather than raw IEEE-754 bit equality. If no row matches, the result is #N/A. The scan direction (top to bottom, first match wins) is what makes the result deterministic when the key repeats.
• Approximate mode (range_lookup = TRUE, or omitted). i is the row whose first-column value is the largest value not exceeding v, under Excel's ordering of values — and this answer is only meaningful if the first column is sorted in ascending order. Microsoft documents the ascending-sort requirement as a caller obligation. It is a precondition, not a checked constraint: Excel does not validate it and raises no error when it is violated.

Approximate mode is the older and, historically, the primary mode: it is what makes VLOOKUP a bracket lookup, the tool for tax bands, shipping tiers, grade boundaries, and any table whose keys are the lower bounds of intervals. Used that way it is exactly the right function. Used as a substitute for exact lookup, it is the single most expensive default in the product.

Arguments

lookup_value — the key to search for. Numbers, text and logicals are all admissible. In exact mode, * and ? in a text lookup value are documented to act as wildcards (any run of characters, and any single character respectively), with ~ as the escape; that is a real behavioural difference from a plain equality test, and it means a lookup key that legitimately contains * or ? must be escaped.

table_array — the table. May be a reference or an array. Only its first column is searched; the remaining columns exist solely as return material. A table_array whose left edge is not where the caller thinks it is produces wrong answers with no diagnostic.

range_lookup — the mode flag. Optional. See below.

Try it

Quirks

The omitted fourth argument

This is the part of VLOOKUP worth stating at length, because the cost is asymmetric and therefore invisible.

Omitting range_lookup selects approximate match. VLOOKUP(v, T, k) and VLOOKUP(v, T, k, TRUE) are the same call. A reader who writes the three-argument form intending "find v in the table" has instead written "find the largest key not exceeding v, trusting that the first column is sorted ascending".

Why this default is expensive is not that it is wrong — it is the right default for the bracket-lookup use it was designed for — but that its failure mode is silent:

An exact-match miss announces itself as #N/A and stops the spreadsheet. An approximate-match miss returns a plausible number of the right type, in the right format, in the right cell. It propagates. It is discovered, if ever, by someone reconciling totals months later.

The unsorted case deserves separate emphasis. Because Microsoft documents only that approximate match on unsorted data "may not return the correct value", the behaviour on unsorted input is undefined by the documentation rather than defined-and-undesirable. What is observable is that it is not random: for a fixed table it is stable and repeatable, which is exactly what makes it survive testing. The shape of those wrong answers is consistent with a bisection over the search column, but the Handbook has not pinned the search procedure and does not assert one.

Two practical consequences the Handbook is willing to state plainly:

1. If you mean exact lookup, pass FALSE (or 0) explicitly, every time. There is no defensible reason to rely on the default when the intent is exact.
2. If you mean bracket lookup, pass TRUE explicitly and say so in a comment, so the next reader knows the sorted-column precondition is load-bearing rather than accidental.

Result and edge cases

The return kind is whatever the selected cell holds — number, text, logical, error, or the zero that a referenced empty cell publishes. VLOOKUP performs no conversion on the value it returns; it is a selector, not a coercer. The general rules for how empty cells, omitted arguments and error values reach a function are in the call pipeline and coercion and lifting; only the function-specific parts are stated here.

• Repeated keys. Exact mode returns the first match scanning downward. Approximate mode's choice among equal keys is not something the Handbook has pinned.
• Mixed types in the search column. Approximate mode needs a total order over values, so a column mixing numbers, text and logicals forces Excel's cross-type ordering into view. Microsoft states such an order on the MATCH page — ascending means -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE, so all numbers precede all text, and text precedes the logicals. Whether VLOOKUP's approximate lane uses that same order is expected but not documented on VLOOKUP's own page, and the Handbook has not verified it.

Errors

As documented by Microsoft (see Sources):

Error values arriving in lookup_value propagate rather than being searched for, which is the universal coercion rule rather than anything special to VLOOKUP. The Handbook has not verified what an error value inside the search column does to either mode.

How to implement

Notes for implementers

• The two modes are different search procedures over the same data, not one procedure with a comparison tweak. An implementation that treats approximate match as "exact match, else nearest" will diverge on unsorted input, which is precisely the case real workbooks hit.
• Blanks in the search column are skippable candidates in the approximate path, not aborts. Getting this wrong turns a common trailing-blank range into a whole-formula #N/A.
• lookup_value must be admitted as an array, with the result spilling elementwise. This is easy to miss when the scalar lane is written first.

Rust code

eager(
    VLOOKUP,
    "VLOOKUP",
    CallableArityShape::range(3, 4),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("table_array"),
        Parameter::value("col_index_num"),
        Parameter::value("range_lookup"),
    ],
    vlookup,
)

/// VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): the value in column
/// col_index_num of the row whose first cell matches; HLOOKUP is the same with rows and columns
/// exchanged. range_lookup is read as a logical; omitted, it is TRUE (the bisection), and blank or
/// given empty, FALSE (an exact match, with wildcards). The index is read as a number and
/// truncated, and checked only after a match: below 1 is #VALUE! (from -2^31 upward), above the
/// table's width (or at or below -2^31) #REF!. A table of several areas, a single value that is
/// not a reference, a blank or omitted table, and no match are #N/A. A blank result cell is Empty.
fn vlookup(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(VLOOKUP, services);
    Site::finish(table_lookup(&site, arguments, services, true))
}

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 and HLOOKUP. XLOOKUP separates the lookup vector from the return vector (so no fragile column index), defaults to exact match, can search backwards, offers an if_not_found value instead of #N/A, and can return to the left of the search column. For new work, XLOOKUP is the function to reach for. Microsoft has not deprecated VLOOKUP — it remains fully supported, and it remains what tens of millions of existing workbooks contain.
• LOOKUP is the older, weaker ancestor: vector and array forms, approximate match only.
• Readers confuse VLOOKUP with FILTER (which returns all matching rows rather than one) and with XMATCH (position, not value).

Named variants

No named variants are recorded yet.