How to Use XLOOKUP in Excel
XLOOKUP arrived in Excel 365 and Excel 2021 as Microsoft's answer to decades of complaints about VLOOKUP's limitations. Where VLOOKUP forces you to structure data with lookup columns on the left and...
Key Insights
- XLOOKUP replaces VLOOKUP and HLOOKUP with a single function that searches in any direction, returns multiple columns, and handles errors gracefully without workarounds
- The function’s optional parameters enable reverse searches, wildcard matching, and approximate matches for range-based lookups like tax brackets or pricing tiers
- XLOOKUP is only available in Excel 365 and Excel 2021+, requiring INDEX/MATCH alternatives for backward compatibility with older versions
Introduction to XLOOKUP
XLOOKUP arrived in Excel 365 and Excel 2021 as Microsoft’s answer to decades of complaints about VLOOKUP’s limitations. Where VLOOKUP forces you to structure data with lookup columns on the left and can only search vertically, XLOOKUP searches in any direction and returns values from any column regardless of position.
The advantages are substantial. VLOOKUP requires you to count columns and breaks when you insert new ones. XLOOKUP references exact ranges, making formulas resilient to structural changes. VLOOKUP returns errors when values aren’t found unless you wrap it in IFERROR. XLOOKUP includes built-in error handling. VLOOKUP can’t search right-to-left without helper columns. XLOOKUP doesn’t care about direction.
Use XLOOKUP when you’re working in Excel 365 or 2021 and need reliable lookups. Stick with INDEX/MATCH only when you need backward compatibility with older Excel versions or when sharing workbooks with users who haven’t upgraded.
Basic XLOOKUP Syntax and Simple Examples
The XLOOKUP function follows this structure:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The first three parameters are required:
- lookup_value: What you’re searching for
- lookup_array: Where to search for it
- return_array: What to return when found
The optional parameters provide powerful functionality:
- if_not_found: Custom message when no match exists
- match_mode: 0 (exact), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard)
- search_mode: 1 (first to last), -1 (last to first), 2 (binary ascending), -2 (binary descending)
Here’s a straightforward employee lookup:
=XLOOKUP(E2, A2:A100, B2:B100)
This searches for the employee ID in E2 within the range A2:A100 and returns the corresponding name from B2:B100. If employee ID 1045 is in cell A25, the formula returns the value from B25.
For a product pricing lookup:
=XLOOKUP(G2, Products[Code], Products[Price], "Product not found")
This searches the Code column in a structured table named Products and returns the price. If the product doesn’t exist, it displays “Product not found” instead of #N/A.
Advanced XLOOKUP Features
XLOOKUP’s optional parameters unlock capabilities that required complex workarounds in VLOOKUP.
Reverse search finds the last occurrence of a value by searching from bottom to top. This is useful for finding the most recent transaction or latest status update:
=XLOOKUP(A2, B2:B1000, C2:C1000, "Not found", 0, -1)
The -1 in the search_mode parameter tells Excel to start from the bottom of the range. If you’re tracking inventory movements and want the current quantity (the last entry for each item), reverse search finds it without sorting.
Wildcard matching enables partial text searches using match_mode 2:
=XLOOKUP("*"&F2&"*", A2:A500, B2:B500, "No match", 2)
This finds any cell in A2:A500 containing the text from F2. The asterisks work as wildcards matching any characters before or after your search term.
Approximate matching handles range-based lookups like tax brackets or volume discounts. Use match_mode -1 to find the exact match or the next smallest value:
=XLOOKUP(D2, TaxBrackets[Threshold], TaxBrackets[Rate], , -1)
If D2 contains 75000 and your thresholds are 0, 10000, 40000, and 85000, this returns the rate for the 40000 bracket because 75000 falls between 40000 and 85000.
Returning Multiple Columns with XLOOKUP
XLOOKUP returns entire rows or multiple columns by expanding the return_array parameter. Instead of referencing a single column, reference multiple adjacent columns:
=XLOOKUP(A2, Employees[ID], Employees[Name]:Employees[Salary])
This single formula returns the name, department, and salary for the employee ID in A2. The formula spills across multiple columns automatically. If you place this in cell B2 and the return range includes three columns, Excel populates B2, C2, and D2 with the results.
For a complete employee information lookup:
=XLOOKUP(F2, A2:A100, B2:E100, "Employee not found")
This searches for an employee ID in F2 and returns four columns of data (B through E) in one operation. The formula is dynamic—if you add columns to your return range, the spill range adjusts automatically.
Nested XLOOKUP and Error Handling
Combine multiple XLOOKUPs for two-dimensional lookups that find values at the intersection of row and column criteria. This replicates INDEX/MATCH/MATCH patterns with clearer syntax:
=XLOOKUP(H2, A2:A50, XLOOKUP(I2, B1:F1, B2:F50))
This performs a matrix lookup where H2 contains the row identifier and I2 contains the column identifier. The inner XLOOKUP finds the correct column, and the outer XLOOKUP finds the correct row within that column.
For a sales commission lookup based on region and performance tier:
=XLOOKUP(A2, Regions, XLOOKUP(B2, Tiers, CommissionRates, "Invalid tier", 0), "Invalid region", 0)
The if_not_found parameter provides context-specific error messages. Instead of generic #N/A errors, users see “Invalid tier” or “Invalid region” depending on which lookup failed.
Custom error handling improves user experience in dashboards and reports:
=XLOOKUP(D2, Orders[OrderID], Orders[Total], "Order ID not found - check your entry", 0)
This guides users toward resolution instead of leaving them confused by error codes.
Common Use Cases and Best Practices
XLOOKUP performs well with datasets containing tens of thousands of rows, but binary search modes (2 or -2) dramatically improve performance on sorted data. For a sorted price list:
=XLOOKUP(A2, Products[SKU], Products[Price], "SKU not found", 0, 2)
The binary search mode (2) requires the lookup array to be sorted ascending but executes much faster than linear search on large datasets.
Structured table references make formulas more readable and maintainable:
=XLOOKUP([@CustomerID], Customers[ID], Customers[Name])
This syntax clearly indicates you’re looking up customer names from a Customers table, and the formula adjusts automatically when you add or remove table rows.
Combine XLOOKUP with data validation for dynamic dashboards. Create a dropdown list of product names, then use XLOOKUP to populate related information:
=XLOOKUP(A2, Products[Name], Products[Description])
=XLOOKUP(A2, Products[Name], Products[Price])
=XLOOKUP(A2, Products[Name], Products[Stock])
Place these formulas in adjacent cells, and they update instantly when users select different products from the dropdown.
Layer XLOOKUP with conditional logic for sophisticated data retrieval:
=IF(C2="Active", XLOOKUP(A2, ActiveList[ID], ActiveList[Value]), XLOOKUP(A2, InactiveList[ID], InactiveList[Value]))
This checks a status field and searches the appropriate list based on the condition.
Troubleshooting and Limitations
XLOOKUP’s biggest limitation is availability. It doesn’t exist in Excel 2019, Excel 2016, or earlier versions. If you share workbooks with users on older versions, they’ll see #NAME? errors where your XLOOKUP formulas should be.
For backward compatibility, use INDEX/MATCH instead:
=INDEX(B2:B100, MATCH(E2, A2:A100, 0))
This replicates basic XLOOKUP functionality in all Excel versions. For multiple return columns:
=INDEX(B2:E100, MATCH(F2, A2:A100, 0), 0)
Common errors and solutions:
#VALUE! error: Your lookup_array and return_array have different numbers of rows. Verify both ranges cover the same number of cells vertically.
#N/A error: The lookup value doesn’t exist and you haven’t specified if_not_found. Add a fourth parameter with a custom message or default value.
Spill error: The formula tries to return multiple values but adjacent cells aren’t empty. Clear the cells where results should spill or move your formula to an area with empty cells.
Incorrect results with approximate match: Your data isn’t sorted correctly for the match_mode you specified. Match_mode -1 requires ascending sort, match_mode 1 requires descending sort.
Check Excel version compatibility before deploying workbooks with XLOOKUP. Test on the oldest Excel version your users might have, or provide alternative versions using INDEX/MATCH for broader compatibility.
XLOOKUP represents a genuine improvement over legacy lookup functions. Its intuitive syntax, flexible search capabilities, and built-in error handling eliminate common pain points. For Excel 365 users, it should be your default choice for lookup operations.