Functions / Lookup & reference / XLOOKUP

XLOOKUP

3652021+

Look up a value in a range or array, and return the matching value from a parallel array. The successor to VLOOKUP and HLOOKUP.

Syntax

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

Argument	Required	Type	Description
lookup_value	yes	any	Value to find in lookup_array.
lookup_array	yes	range	The array or range to search.
return_array	yes	range	The array or range to return a value from.
if_not_found	—	any	Returned when no match. If omitted, returns #N/A.
match_mode	—	0\|-1\|1\|2	Exact (0, default), next smaller (-1), next larger (1), wildcard (2).
search_mode	—	1\|-1\|2\|-2	First-to-last (1, default), last-to-first (-1), binary asc (2), desc (-2).

Find a region by employee id, returning "Unknown" when no match exists.

=XLOOKUP(A2, Employees[id], Employees[region], "Unknown")

Two-dimensional lookup — XLOOKUP returning a row, then XLOOKUP again on that row.

=XLOOKUP("Q4",
  Headers,
  XLOOKUP(A2, Names, DataTable)
)

#N/A

If no match and if_not_found is omitted, returns #N/A. Wrap with IFERROR only as a last resort — the error often points to a real bug.

#VALUE!

lookup_array and return_array must be the same length. Mismatched dimensions raise #VALUE!, not #N/A.