📎 This article includes 1 downloadable practice file ↓

LookupLevel: IntermediateAvailable in: Excel 365 / 2021+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. Example 4
7. Power combo
8. Common errors and fixes
9. Related functions

XLOOKUP finds a value in one range and returns the matching item from another. It looks in any direction, defaults to exact match, and has a built-in “not found” result.

Syntax

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

Argument	What it means
lookup_value	What to find.
lookup_array	Where to find it.
return_array	What to return (can be several columns).
if_not_found	Result when missing.
match_mode	0 exact (default), −1 next smaller, 1 next larger, 2 wildcard.
search_mode	1 first-to-last, −1 last-to-first.

Examples

Example 1

=XLOOKUP(F2, A:A, C:C, "Not found")

Simple exact lookup.

Example 2

=XLOOKUP(F2, C:C, A:A)

Looks to the left — impossible with VLOOKUP.

Example 3

=XLOOKUP(F2, A:A, B:D)

Returns three columns at once (spills).

Example 4

=XLOOKUP(F2, A:A, C:C, , 0, -1)

Last match — latest price for a product.

Power combo

=XLOOKUP(1, (A:A=F2)*(B:B=G2), C:C, "None")

Two-condition lookup without helper columns.

Common errors and fixes

You see	Why, and the fix
#NAME?	The file was opened in Excel 2019 or older, which has no XLOOKUP.
#VALUE!	lookup_array and return_array have different heights.

Related functions

VLOOKUP · INDEX · XMATCH · FILTER

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

📎 Practice files for this article

• 📗
  Lookup scenarios workbookVLOOKUP, HLOOKUP, INDEX/MATCH and XLOOKUP side by side — vertical, horizontal, two-way — plus 6 real failure cases and their fixes, with PASS/FAIL checks.
  ⬇ XLSX · 10 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

Keep reading

Excel Functions A–ZCOUNTIF Function in Excel: Count Cells That Meet a ConditionExcel Functions A–ZIFERROR Function in Excel: Replace Errors With Something FriendlyExcel Functions A–ZINDEX Function in Excel: Return the Value at a Row and Column Position