xlookup

Modern lookup with optional default and search direction.

Syntax

v = xlookup(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)

Description

Searches lookup_array for lookup_value and returns the corresponding entry from return_array.

• if_not_found — default returned if no match (otherwise raises #N/A).
• match_mode — 0 (exact, default), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard).
• search_mode — 1 (first to last, default), -1 (last to first), 2/-2 (binary search ascending/descending).

Replaces vlookup/hlookup for most use cases — works in any direction, no sorting required, no column-index counting.

Examples

xlookup('apple', A1:A100, B1:B100)
xlookup('apple', A1:A100, B1:B100, 'not found')

See also

• xmatch — Modern match with full search-mode controls.
• vlookup — Vertical lookup in a table.
• hlookup — Horizontal lookup in a table.
• lookup — Approximate-match lookup in a vector or array.
• match — Position of a value in a vector.