Quick Navigation
XLOOKUP Function
Summary
The XLOOKUP function performs powerful lookups by searching a specified range or array and returning corresponding values from another range. Unlike traditional lookup functions, XLOOKUP works in any direction, handles multiple return columns, and provides advanced matching and search options for precise results.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| lookup_value | Any |
Yes | Value to find in lookup_array |
| lookup_array | Range |
Yes | Search range containing lookup values |
| return_array | Range |
Yes | Return range with corresponding values |
| if_not_found | Any |
No | Custom result when no match found |
| match_mode | Number |
No | 0=exact, -1=next smaller, 1=next larger, 2=wildcard |
| search_mode | Number |
No | 1=first-last, -1=last-first, 2=binary asc, -2=binary desc |
Using the XLOOKUP Function
XLOOKUP revolutionizes Excel lookups by replacing VLOOKUP, HLOOKUP, and INDEX/MATCH combinations. It searches any lookup_array and returns matching values from return_array, regardless of column position. Use it for simple lookups, multi-column returns, approximate matches, reverse searches, or optimized binary searches on sorted data.
Common XLOOKUP Examples
Basic Country Code Lookup
=XLOOKUP(F2,B2:B11,D2:D11)Finds country name in F2 and returns corresponding telephone country code from column D.
Multi-Column Employee Lookup
=XLOOKUP(B2,B5:B14,C5:D14,"Not found")Returns both employee name and department based on Employee ID, with custom no-match text.
Approximate Tax Rate Match
=XLOOKUP(E2,C2:C7,B2:B7,0,1,1)Finds tax rate bracket for income in E2 using next-larger match (match_mode=1).
Nested 2D Table Lookup
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))Performs vertical and horizontal lookup to find value at row/column intersection.
Sum Range Between Lookups
=SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))Sums all values between two lookup positions (grapes through pears).
Frequently Asked Questions
Common Errors and Solutions
#N/A - No match found
Cause: lookup_value not present in lookup_array
Solution: Verify data exists or use appropriate match_mode
Incorrect results with binary search
Cause: lookup_array not sorted for search_mode 2 or -2
Solution: Sort data ascending (2) or descending (-2) or use search_mode 1
#VALUE! error
Cause: lookup_array and return_array different sizes
Solution: Ensure arrays have matching row counts
Notes
- Available only in Excel 365 and Excel 2021+
- Binary search (search_mode 2/-2) requires sorted data
- Can replace VLOOKUP, HLOOKUP, INDEX/MATCH
- Supports dynamic arrays
- Wildcard matching uses * (any chars), ? (single char), ~ (literal wildcard)
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2016, Excel 2019, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021+