XLOOKUP

XLOOKUP: Looks up a value in one array and returns the aligned value from another array.

Summary

XLOOKUP supports exact, approximate, and wildcard matching with forward or reverse search.

Remarks

Defaults: match_mode=0 (exact), search_mode=1 (first-to-last).
if_not_found is optional; if omitted and no match exists, returns #N/A.
match_mode: 0 exact, -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard.
search_mode: 1 forward, -1 reverse. Other modes are accepted with current fallback behavior.
lookup_array must be 1D. Invalid shape returns #VALUE!.
If return_array is multi-column or multi-row, the matched row/column is returned as a spill.

Examples

Exact key lookup with fallback

Grid

Cell	Value

Formula

Result

Not evaluated yet.

Expected

Return a full row from a matched key

Grid

Cell	Value

Formula

Result

Not evaluated yet.

Expected

[["West",140]]

FAQ

How do match_mode and search_mode interact?

match_mode controls exact/approximate/wildcard behavior, while search_mode controls scan direction; reverse search (-1) returns the last matching position.

What happens when no match is found?

If if_not_found is provided, XLOOKUP returns that value; otherwise it returns #N/A.

Why do I get #VALUE! from XLOOKUP on valid ranges?

The lookup_array must be one-dimensional (single row or single column); multi-row-and-column lookup ranges return #VALUE!.