Practical guide

Excel XLOOKUP: Exact Matches and Useful Options

XLOOKUP separates the lookup range from the return range. It can search in either direction and lets you specify what to show when no match is found.

What to do

Use =XLOOKUP(E2,A2:A100,C2:C100,"Not found") to find E2 in column A and return the corresponding value from column C.
The default match mode is exact. Add optional arguments only when you specifically need a wildcard, next-smaller, or next-larger match.
Use the not-found argument to display a helpful message instead of #N/A, while keeping that message distinct from real data.
Test duplicate keys and missing keys so you know whether the first matching result is appropriate for your data.

XLOOKUP is unavailable in some older Excel versions; a workbook opened there may not calculate the function.
Lookup and return arrays should have compatible sizes and orientation.

Use XLOOKUP to search a lookup range and return a corresponding value, including from columns on either side.

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

=XLOOKUP(E2,A2:A5,C2:C5,"Not found")

Example result: 120 for Widget (first match).

Item	Region	Sales	Status
Widget	North	120	Paid
Cable	South	80	Paid
Widget	North	200	Pending
Desk	North	150	Paid

Find a value by product or employee ID without counting a return-column index.

VLOOKUP supports older Excel but only searches to the right. INDEX with MATCH works in many older versions.

XLOOKUP is available in Microsoft 365 and newer Excel releases; it is not available in some older perpetual versions.

Does XLOOKUP require an exact-match argument?

No. Exact match is the default; optional arguments change match or search behavior.

What happens when the key is missing?

Use the optional if_not_found argument to provide a clear fallback message.