HLOOKUP

What it does

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) searches the top row of table_array for lookup_value, and returns the value in the same column from the row given by row_index_num, counting the top row as row 1.

"Searches" means one of two genuinely different algorithms, chosen by range_lookup:

Exact match (range_lookup = FALSE). A linear scan of the top row for a value equal to lookup_value; the first match wins. Microsoft documents that in this mode wildcards are honoured in a text lookup_value: ? matches any single character, * any run of characters, and ~ escapes a literal ? or *. If nothing matches, the result is #N/A.

Approximate match (range_lookup = TRUE, or omitted). The top row is assumed sorted in ascending order, and the function finds the largest value that is less than or equal to lookup_value. This is a range lookup in the literal sense — it is how tax bands, grade boundaries and shipping tiers are expressed. If lookup_value is smaller than every value in the top row, the result is #N/A.

The assumption in the second mode is not checked, and this is the function's defining hazard. Microsoft documents that the values must be sorted ascending; on unsorted data the function does not error, it simply returns something. What it returns depends on the search strategy the implementation uses, which is why "approximate match on unsorted data" is a bug that survives review — the answer looks like an answer.

Arguments

The two positions that are routinely misunderstood:

Try it

Quirks

Result and edge cases

Return kind: whatever the located cell holds — number, text, logical, error, or a blank normalized under the publication rules in the value universe.

Edge cases specific to this function:

• Duplicate keys. On the exact lane the first match wins. On the approximate lane, which of several equal keys is found depends on the search strategy, and the Handbook has not established Excel's choice.
• Mixed types in the search row. Excel's ordering across numbers, text and logicals is a total order, but where the type boundaries fall governs what "largest value less than or equal" means on a mixed row. Not established here.
• Text comparison is case-insensitive in Excel's usual convention, and the collation used for the approximate lane's ordering may be locale-dependent. Not established here.
• Empty cells in the search row — skipped, matched by an empty lookup value, or treated as a zero — is a real question for approximate match over a partially filled table.

Errors

Microsoft's page documents the error surface:

The #VALUE! / #REF! split on row_index_num is worth keeping straight: too small is a bad argument, too large is a bad reference. That asymmetry is inherited from the C-API era and is shared with VLOOKUP.

An error value in lookup_value propagates under the universal rule in coercion and lifting. An error value sitting in the located cell is simply the result — it is a value like any other.

How to implement

Notes for implementers

• The two lanes are two algorithms and must be tested as two functions. Exact match is a linear scan with wildcard support; approximate match is an ordered search. Sharing one code path between them is where wildcard characters start leaking into the sorted lane, or where the sorted lane's early exit silently truncates an exact scan.
• Do not validate sortedness. Excel does not, and an implementation that errors on unsorted input will reject workbooks that Excel computes. The obligation is to match the observable answer, including on unsorted data — which means the search strategy itself is observable and cannot be freely chosen.
• Wildcard escaping. ~ before ? or * makes the character literal. A naive glob-to-regex translation that does not honour ~ will match the wrong cells and never error.
• Selective dereference. The reference-aware preparation exists so that a lookup over a large table_array need not materialize it. The shared model records this as function-local and not generalized, so an implementation should treat it as a permitted optimization within this function rather than as a pipeline guarantee.

Rust code

eager(
    HLOOKUP,
    "HLOOKUP",
    CallableArityShape::range(3, 4),
    &[
        Parameter::value("lookup_value"),
        Parameter::value_or_reference("table_array"),
        Parameter::value("row_index_num"),
        Parameter::value("range_lookup"),
    ],
    hlookup,
)

fn hlookup(
    arguments: &[CalcValue],
    services: &mut EvalServices<'_>,
) -> Result<CalcValue, FunctionFault> {
    let site = Site::new(HLOOKUP, services);
    Site::finish(table_lookup(&site, arguments, services, false))
}

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 for both. It searches in either direction, defaults to exact match, takes an explicit not-found argument instead of #N/A, and separates the search array from the return array so row_index_num disappears entirely. New code should use it; Microsoft retains HLOOKUP for workbook compatibility.
• INDEX + MATCH is the classic decomposition: MATCH finds the position, INDEX retrieves it. It removes the index-counting fragility and lets the search row sit anywhere in the table rather than being forced to the top.
• LOOKUP's array form does something similar and is documented by Microsoft as provided chiefly for compatibility with other spreadsheet programs; HLOOKUP is the better-specified choice.
• MATCH alone carries the same three-way match-type argument and the same sorted-data requirement for its approximate modes, so the hazard described above is shared.

Named variants

No named variants are recorded yet.