XLOOKUP: Modern Lookup Function (2025)
Quick Answer: XLOOKUP replaces VLOOKUP: =XLOOKUP(lookup_value, lookup_array, return_array). Works any direction. Built-in error handling. Returns arrays. Excel 365/2021 only. Example: =XLOOKUP(A2, Names, Prices, "Not Found").
What is XLOOKUP?
XLOOKUP is Excel's modern replacement for VLOOKUP and HLOOKUP. It searches any direction, handles errors gracefully, and returns dynamic arrays. Available in Excel 365 and Excel 2021.
VLOOKUP vs XLOOKUP:
Old: VLOOKUP
=VLOOKUP(A2, B:D, 3, FALSE)
- • Only searches right
- • Column index required
- • Returns error if not found
- • Breaks when columns change
New: XLOOKUP
=XLOOKUP(A2, B:B, D:D, "Not Found")
- • Searches any direction
- • Direct return array
- • Built-in error handling
- • Column-order independent
Why XLOOKUP is Better:
✓ Bidirectional
Search left, right, up, or down
✓ Error Handling
Custom message instead of #N/A
✓ Dynamic Arrays
Return multiple rows/columns
✓ Wildcard Search
Pattern matching built-in
✓ Exact/Approximate
Multiple match modes
✓ Search Direction
First-to-last or last-to-first
Simple Example:
Data:
| Name (B) | Price (C) | Stock (D) |
|---|---|---|
| Apple | $1.50 | 100 |
| Banana | $0.75 | 200 |
| Orange | $2.00 | 150 |
=XLOOKUP("Banana", B:B, C:C)Returns: $0.75
Searches column B for "Banana", returns corresponding value from column C
⚠️ Version Requirement
XLOOKUP requires:
- • Excel 365 (subscription)
- • Excel 2021 (perpetual license)
- • Excel for Mac 2021+
Not available: Excel 2019, 2016, 2013, 2010, or earlier. Use VLOOKUP or INDEX/MATCH instead.
💡 Common Use Cases
- • Product lookups: Find prices, descriptions, stock levels
- • Employee data: Retrieve salaries, departments, contact info
- • Customer records: Pull addresses, order history, status
- • Inventory management: Check stock, reorder points, suppliers
- • Financial data: Find account balances, transaction details
XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Required Arguments
lookup_value (required)
The value to search for
A2 or "Apple" or 12345lookup_array (required)
The array to search in (single row or column)
B:B or Names[Column] or $A$2:$A$100return_array (required)
The array to return values from (can be multiple columns)
C:C or Prices[Column] or $C$2:$E$100Optional Arguments
if_not_found (optional)
Value to return if no match found (default: #N/A)
"Not Found" or 0 or ""match_mode (optional)
How to match (default: 0 - exact match)
0 - Exact match (default)
-1 - Exact or next smallest
1 - Exact or next largest
2 - Wildcard match (* and ?)
search_mode (optional)
Search direction (default: 1 - first-to-last)
1 - Search first-to-last (default)
-1 - Search last-to-first
2 - Binary search (ascending order required)
-2 - Binary search (descending order required)
Common Patterns
Basic Lookup
=XLOOKUP(A2, Names, Prices)
With Error Handling
=XLOOKUP(A2, Names, Prices, "Not Found")
Multiple Return Columns
=XLOOKUP(A2, Names, B:D)
Wildcard Search
=XLOOKUP("*apple*", Products, Prices, , 2)
Last Match (Latest Entry)
=XLOOKUP(A2, IDs, Dates, , 0, -1)
⚠️ Important Notes
- • Dynamic arrays: XLOOKUP can spill multiple results automatically
- • No column counting: Reference return array directly, no column index needed
- • Bi-directional: Lookup and return arrays don't need to be adjacent
- • Performance: Binary search (mode 2/-2) is fastest for sorted data
- • Error handling: if_not_found parameter eliminates need for IFERROR
- • Wildcards: Must set match_mode to 2 for * and ? patterns
12+ XLOOKUP Examples
Basic Lookups
1. Simple Product Lookup
=XLOOKUP(A2, ProductNames, Prices)
Find price by product name
2. With Error Message
=XLOOKUP(A2, IDs, Names, "ID Not Found")
Returns custom message instead of #N/A
3. Return Multiple Columns
=XLOOKUP(A2, EmployeeID, B:E)
Returns Name, Department, Salary, and Status in one formula
4. Left Lookup (Replace VLOOKUP)
=XLOOKUP(D2, PriceColumn, ProductColumn)
Search right column, return left column (impossible with VLOOKUP)
Advanced Match Modes
5. Approximate Match (Next Smallest)
=XLOOKUP(A2, Breakpoints, Grades, , -1)
Grade lookup: 85 finds "80-89 = B" range
6. Wildcard Search
=XLOOKUP("*"&A2&"*", Products, Prices, "Not Found", 2)Partial match: "phone" finds "iPhone 15"
7. Case-Insensitive Exact Match
=XLOOKUP(UPPER(A2), UPPER(Names), Emails)
"john" matches "JOHN" or "John"
Search Direction
8. Last Match (Most Recent)
=XLOOKUP(A2, CustomerIDs, OrderDates, , 0, -1)
Find most recent order date (search from bottom)
9. Binary Search (Fast)
=XLOOKUP(A2, SortedIDs, Data, , 0, 2)
Ultra-fast search on sorted data (ascending)
Two-Way Lookups
10. Nested XLOOKUP (Matrix Lookup)
=XLOOKUP(A2, RowHeaders, XLOOKUP(B2, ColHeaders, DataRange))
Lookup by both row and column (like INDEX/MATCH/MATCH)
11. Horizontal Lookup
=XLOOKUP(A2, 1:1, 5:5)
Replace HLOOKUP: search row 1, return row 5
12. Dynamic Array Return
=XLOOKUP(A2:A10, IDs, Prices)
Lookup multiple values at once (spills automatically)
🎯 Pro Tips
- • Replace INDEX/MATCH: XLOOKUP is simpler and more readable
- • No helper columns: Return from non-adjacent columns directly
- • Dynamic arrays: Results automatically spill to neighboring cells
- • Binary search: Use mode 2/-2 for sorted lists (10x+ faster)
- • Last occurrence: Search mode -1 finds duplicates from bottom
- • Structured references: Use table syntax for clearer formulas
Frequently Asked Questions
Can I use XLOOKUP in Excel 2019?
No. XLOOKUP requires Excel 365, Excel 2021, or Excel for Mac 2021+. It's not available in Excel 2019, 2016, 2013, or earlier versions. For older versions, use VLOOKUP, INDEX/MATCH, or upgrade to Excel 365 subscription.
How do I replace my VLOOKUP formulas with XLOOKUP?
Convert =VLOOKUP(value, table, col, 0) to =XLOOKUP(value, lookup_column, return_column). Example: =VLOOKUP(A2,B:D,3,0) becomes =XLOOKUP(A2,B:B,D:D). No column counting needed, direct column references, and built-in error handling.
Why is my XLOOKUP returning #SPILL error?
#SPILL means XLOOKUP wants to return multiple values but neighboring cells aren't empty. Clear cells to the right/below. Or use @ to force single value: =@XLOOKUP(...). Common when returning multiple columns or using array inputs.
Can XLOOKUP return multiple rows or columns?
Yes! XLOOKUP supports dynamic arrays. Return multiple columns: =XLOOKUP(A2, IDs, B:E) returns 4 columns. Return multiple rows: =XLOOKUP(A2:A5, IDs, Names) returns 4 rows. Results spill automatically to neighboring cells.
What's the difference between XLOOKUP and INDEX/MATCH?
XLOOKUP is simpler: =XLOOKUP(value, lookup, return). INDEX/MATCH is complex: =INDEX(return, MATCH(value, lookup, 0)). XLOOKUP has built-in error handling, approximate match, and search direction. Use XLOOKUP if you have Excel 365/2021.
How do I do a two-way lookup with XLOOKUP?
Nest XLOOKUP: =XLOOKUP(row_value, row_array, XLOOKUP(col_value, col_array, data_range)). Inner XLOOKUP finds column, outer finds row. Example: =XLOOKUP(A2, Names, XLOOKUP(B2, Months, SalesData)) finds sales by name and month.