Skip to content

XLOOKUP

Lookup & referenceMicrosoft 365

Modern lookup — any direction, exact by default, built-in not-found value.

Syntax

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

Arguments

lookup_value
What to search for.
lookup_array
The single column/row to search in.
return_array
The column/row to return from (same size as lookup_array).
if_not_foundoptional
Value to return instead of #N/A.
match_modeoptional
0 exact (default), -1 exact or next smaller, 1 next larger, 2 wildcard.
search_modeoptional
1 first-to-last (default), -1 last-to-first, 2/-2 binary search.

Examples

=XLOOKUP(A2, Products[Code], Products[Price], "n/a")

→

=XLOOKUP(A2, ids, data, , -1)

→ banded lookup (≥ match) — e.g. tax slabs

=XLOOKUP(1, (region=A2)*(year=B2), sales)

→ two-criteria lookup

Good to know

  • Return an entire row/block by passing a multi-column return_array.
  • Replaces VLOOKUP, HLOOKUP and most INDEX/MATCH. Needs Microsoft 365 / Excel 2021.

Related functions

VLOOKUPINDEXMATCHXMATCHFILTER

More Lookup & reference functions

ADDRESSAREASCHOOSECOLUMNCOLUMNSFORMULATEXTGETPIVOTDATAHLOOKUPHYPERLINKINDEXINDIRECTLOOKUP

← All 510 functions

Syntax follows Microsoft’s documentation. Examples are written to be pasted into a cell and checked. Argument separators may be ; in your locale. The AI explainer runs on a free community model — verify anything important.