XLOOKUP Function in Excel
Master XLOOKUP with practical examples and error solutions. Excel's modern lookup function replacing VLOOKUP with advanced features.
Excel
Google SheetsExcel=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Quick Answer
XLOOKUP function XLOOKUP function is Excel's modern all-in-one lookup function that searches for a value in a range and returns a corresponding value from another range. It returns exact matches by default and can search in any direction—left, right, up, or down.
Practical Examples
Basic Product Price Lookup
Find the price of a product using its SKU code
Employee Department with Error Handling
Look up employee department with custom 'Not Found' message
Last Purchase Date Lookup (Reverse Search)
Find the most recent purchase date for a customer
Wildcard Search for Partial Matches
Find products containing specific text patterns
Return Multiple Columns (Spill Range)
Retrieve complete employee record with single formula
Approximate Match for Tax Brackets
Find applicable tax rate based on income level
Common Errors and Solutions
XLOOKUP cannot find the lookup value
The lookup value doesn't exist in the lookup array, or there's a data type mismatch
1. Check for extra spaces using TRIM() 2. Verify data types match (numbers vs text) 3. Use the if_not_found parameter for custom message 4. Enable wildcard matching if searching for partial matches
Always validate lookup values exist and use consistent data formatting
Example:
XLOOKUP receives incompatible array sizes
The lookup_array and return_array have different sizes or dimensions
1. Ensure lookup_array and return_array have the same number of rows/columns 2. Check that ranges don't include headers inconsistently 3. Verify you're not mixing rows and columns
Always select equal-sized ranges for lookup and return arrays
Example:
Excel doesn't recognize XLOOKUP function
XLOOKUP is not available in your Excel version (requires Excel 365 or 2021+)
1. Upgrade to Excel 365 or Excel 2021 2. Use alternative: INDEX/MATCH combination 3. Use VLOOKUP if data structure permits 4. Consider using FILTER function as alternative
Check Excel version compatibility before implementing XLOOKUP
Example:
XLOOKUP can't spill results due to obstruction
When returning multiple values, the spill range contains data or merged cells
1. Clear the cells where results need to spill 2. Remove merged cells in the spill area 3. Use single-cell return instead of array 4. Move formula to empty area
Keep adequate empty space for dynamic array results
Example:
Advanced Tips and Techniques
Performance Optimization with Binary Search
For sorted data exceeding 10,000 rows, use binary search mode (2 or -2) to dramatically improve performance. Binary search can be up to 10x faster than linear search on large datasets. Ensure data is sorted appropriately: ascending for mode 2, descending for mode -2.
Avoid Volatile References
Using entire column references (A:A) with XLOOKUP can slow down your workbook. Instead, use defined ranges or dynamic named ranges to limit the search area. This is especially important in workbooks with multiple XLOOKUP formulas.
Combine with FILTER for Multiple Matches
While XLOOKUP returns only the first or last match, combine it with FILTER to retrieve all matching records. This powerful combination eliminates the need for complex array formulas.
Version Compatibility Strategy
When sharing workbooks with users on older Excel versions, provide alternative formulas in adjacent cells or create a compatibility mode sheet using INDEX/MATCH. Document the Excel version requirement clearly.
Nested XLOOKUP for 2D Lookups
Use nested XLOOKUP functions to perform two-dimensional lookups, replacing complex INDEX/MATCH/MATCH formulas. The outer XLOOKUP finds the row, the inner finds the column.
Need Help with XLOOKUP Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula: