XLOOKUP Function in Excel: The Modern Lookup That Replaces VLOOKUP

📎 This article includes 2 downloadable practice files ↓

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. XLOOKUP in practice
10. Where you will use it
11. Worked example
12. Mistakes people make
13. Related functions
14. Continue learning

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.

XLOOKUP in practice

Where you will use it

• Lookups in any direction
• Default value when not found
• Last match with search_mode -1

Worked example

Code	Formula	Result
P02	=XLOOKUP(A2,{"P01";"P02"},{250;400},"Not found")	400
P09	=XLOOKUP(A3,{"P01";"P02"},{250;400},"Not found")	Not found
P01	=XLOOKUP(A4,{"P01";"P02"},{250;400},"Not found")	250

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Arrays of different sizes	Lookup and return arrays must match
Older Excel	Needs 2021 or 365

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.

Continue learning

All Lookup & Reference guides →

Excel #N/A Error in VLOOKUP and XLOOKUP: 5 Fixes That Actually WorkThe #N/A ('Value Not Available') error in Microsoft Excel signals that a lookup function—such…TOROW Function in Excel: Flatten Into One RowTOROW flattens a block into a single row, optionally skipping blanks and errors.WRAPCOLS Function in Excel: Fold a List Into ColumnsWRAPCOLS takes a long list and wraps it into columns of a fixed height…WRAPROWS Function in Excel: Reshape a List Into a GridWRAPROWS reshapes a single column into rows of a fixed width — e.g. a…

📎 Practice files for this article

⬇ Download all 2 files (ZIP · 39 KB)

• 📗
  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
• 📗
  Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 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.