XLOOKUP Has Changed How We Look Up Data in Excel
VLOOKUP has been the workhorse of Excel data lookup for decades, but it carries well-documented limitations: it only searches left-to-right, requires the lookup column to be the leftmost in the range, returns only the first match, and breaks when columns are inserted into the reference range. XLOOKUP, introduced in Office 2019 for Microsoft 365 subscribers and brought into the perpetual versions from Office 2024 onwards, solves all of these problems and adds capabilities that were previously only possible with complex INDEX/MATCH combinations.
This guide goes beyond the standard introductory examples to cover XLOOKUP’s full argument set, multiple-result scenarios, nested lookups, error handling, array returns, and combining XLOOKUP with other modern Excel functions for genuinely powerful data retrieval. XLOOKUP is available in Office 2024 Professional Plus (€34.99). Note that it is not available in Office 2021 or 2019 perpetual versions — those users will need to use INDEX/MATCH or upgrade to access XLOOKUP natively.
XLOOKUP Syntax: All Six Arguments
XLOOKUP has the following syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The first three arguments are required; the last three are optional but powerful.
- lookup_value — The value you are searching for.
- lookup_array — The column (or row) to search in.
- return_array — The column (or row) to return the result from.
- [if_not_found] — Text or value to return if no match is found. Replaces the need for a wrapping IFERROR.
- [match_mode] — 0 (exact match, default), -1 (exact match or next smaller value), 1 (exact match or next larger value), 2 (wildcard match).
- [search_mode] — 1 (first to last, default), -1 (last to first), 2 (binary search ascending), -2 (binary search descending).
Technique 1: Handling Not-Found Results Cleanly
The fourth argument eliminates the need for wrapping XLOOKUP in IFERROR, making formulas shorter and more readable:
=XLOOKUP(A2, ProductTable[ProductID], ProductTable[Price], "Not found")If the product ID in A2 does not exist in the ProductID column, the formula returns “Not found” instead of an error. You can use any value here — an empty string (""), a zero (0), or a more informative message like "Check product code".
Technique 2: Returning Multiple Columns in a Single Formula
Unlike VLOOKUP, which returns a single column value, XLOOKUP can return an entire row of data in one formula. Instead of specifying a single column as the return_array, specify a multi-column range:
=XLOOKUP(A2, EmployeeTable[EmployeeID], EmployeeTable[[Name]:[Department]:[Salary]], "Not found")This single formula spills across three adjacent cells, returning the Name, Department, and Salary for the matched employee. This is a dynamic array behaviour — the formula sits in one cell and the results fill the cells to the right automatically. No need to write three separate VLOOKUP formulas or lock column index numbers.
Technique 3: Approximate Matches for Banded Values
XLOOKUP’s match_mode argument makes approximate matching much more intuitive than VLOOKUP’s equivalent. Consider a discount rate table:
| Order Value (€) | Discount Rate |
|---|---|
| 0 | 0% |
| 500 | 5% |
| 1000 | 10% |
| 5000 | 15% |
To find the discount for a specific order value, use match_mode -1 (next smaller value):
=XLOOKUP(B2, DiscountTable[Minimum Order], DiscountTable[Discount Rate], "N/A", -1)An order value of €750 will match the €500 tier (the largest value not exceeding €750) and return 5%. The table does not need to be sorted in any particular way, unlike VLOOKUP’s approximate match which requires ascending sort order. Using match_mode 1 (next larger value) would instead return the discount for the next tier up — useful for ceiling-based calculations.
Technique 4: Last-to-First Search for Most Recent Records
The search_mode argument is a significant advantage over INDEX/MATCH and VLOOKUP. Setting search_mode to -1 searches from the bottom of the range upward, returning the last match rather than the first. This is invaluable when working with time-series data where you want the most recent entry for a given identifier:
=XLOOKUP(C2, SalesLog[CustomerID], SalesLog[LastPurchaseDate], "No purchases", 0, -1)If the SalesLog table has multiple rows for the same CustomerID (one per transaction), this formula returns the date from the last row matching that ID — the most recent purchase. Previously achieving this required a complex MAX/IF combination or a helper column.
Technique 5: Wildcard Matching
Setting match_mode to 2 enables wildcard matching, allowing partial string lookups. The standard wildcards apply: * for any sequence of characters and ? for any single character.
=XLOOKUP("*" & D2 & "*", ProductTable[ProductName], ProductTable[SKU], "Not found", 2)If D2 contains “Widget”, this formula finds the first product whose name contains the word “Widget” anywhere in the string — matching “Blue Widget”, “Widget Pro”, or “Standard Widget Kit”. This is useful for imprecise lookup scenarios where the search term might be a fragment of the full value in the source table.
Technique 6: Two-Way Lookup (Row and Column Simultaneously)
XLOOKUP can replace the MATCH component of a two-way INDEX/MATCH lookup, producing an elegant two-way lookup when nested:
=XLOOKUP(G2, SalesMatrix[Month], XLOOKUP(H2, SalesMatrix[#Headers], SalesMatrix))The inner XLOOKUP finds the correct column (by matching the salesperson name in H2 against the header row), and the outer XLOOKUP finds the correct row (by matching the month in G2). The intersection gives the sales figure for that salesperson in that month. This replaces the more complex =INDEX(range, MATCH(row_value, row_range, 0), MATCH(col_value, col_range, 0)) pattern that many Excel users rely on.
Technique 7: Using XLOOKUP with Dynamic Arrays
XLOOKUP works naturally with other dynamic array functions available in Office 2024, enabling sophisticated data retrieval patterns.
Combining with FILTER
=FILTER(EmployeeTable, EmployeeTable[Department]=XLOOKUP(A2, DeptTable[Code], DeptTable[FullName]))Here XLOOKUP looks up the full department name from a code, and FILTER then returns all employee rows in that department. The result is a dynamic array that updates automatically as data changes.
Combining with SORT
=SORT(XLOOKUP(A2:A10, ProductTable[SKU], ProductTable[[Name]:[Price]], "Not found"), 2, -1)XLOOKUP returns multiple rows of data (one for each SKU in A2:A10), and SORT then orders the results by the second column (Price) in descending order. This chain of dynamic array functions eliminates the need for helper columns or pivot tables for many analysis tasks.
Technique 8: Returning an Entire Row When the Lookup Finds a Match
You can use XLOOKUP to retrieve an entire table row and feed it to other functions. This is particularly useful for building dynamic summaries:
=SUM(XLOOKUP(B2, BudgetTable[Department], BudgetTable[[Jan]:[Dec]]))XLOOKUP returns the full 12-month budget row for the specified department, and SUM adds all 12 months — giving the annual budget total in a single formula without knowing the exact column positions or writing a range spanning the entire row manually.
Technique 9: Error-Proofing Complex Nested XLOOKUP
When nesting XLOOKUP formulas for two-way or multi-step lookups, errors in any stage propagate through the chain. Use the if_not_found argument at each level:
=XLOOKUP(G2, RegionTable[Code], XLOOKUP(H2, RegionTable[#Headers], RegionTable, "Column not found"), "Region not found")The inner XLOOKUP handles missing column headers, and the outer handles missing region codes. Both produce human-readable messages rather than #N/A errors.
Technique 10: Binary Search for Large Datasets
For tables with hundreds of thousands of rows, XLOOKUP’s binary search modes (search_mode 2 or -2) offer a significant performance improvement over the default linear search. Binary search requires the lookup array to be sorted (ascending for mode 2, descending for -2), but executes in logarithmic rather than linear time — searching a million-row table requires at most about 20 comparisons instead of up to a million.
=XLOOKUP(A2, LargeTable[ID], LargeTable[Value], "Not found", 0, 2)The 0 in the match_mode position specifies exact match, and 2 in search_mode specifies binary search ascending. Use this pattern whenever your lookup table is sorted and contains more than roughly 50,000 rows.
Common Mistakes to Avoid
- Mismatched array sizes — If lookup_array and return_array have different lengths, XLOOKUP returns a #VALUE! error. Always verify that both arrays reference the same number of rows (or columns, for horizontal lookups).
- Binary search on unsorted data — Using search_mode 2 on an unsorted column produces incorrect results without an error message. Only use binary search when you know the data is properly sorted.
- Forgetting that match_mode 2 is wildcards, not regex — XLOOKUP wildcard matching uses * and ? only. Regular expression patterns are not supported without VBA.
- Version compatibility — XLOOKUP is not available in Excel 2019 or 2021 perpetual versions. If your workbooks will be shared with users on those versions, the cells containing XLOOKUP will show #NAME? errors. Consider using INDEX/MATCH for cross-version compatibility.
XLOOKUP vs VLOOKUP: When to Still Use VLOOKUP
For workbooks that must be compatible with Excel 2016, 2019, or 2021, VLOOKUP remains the appropriate choice. XLOOKUP should be your default for any workbook used exclusively in Office 2024 or Microsoft 365. The performance of both functions on typical business datasets is comparable; XLOOKUP’s advantage is flexibility and readability, not raw speed in most cases.
With Office 2024 Professional Plus available for just €34.99, accessing XLOOKUP alongside other modern functions like LAMBDA, LET, and the full dynamic array function set has never been more affordable. The investment in learning these techniques — applicable across Excel 2024 and Microsoft 365 — will continue paying dividends for the full nine-year support lifetime of the Office 2024 release.
Troubleshooting XLOOKUP: Common Issues and Solutions
#N/A Errors
An #N/A error in XLOOKUP means no match was found and no if_not_found argument was provided. The simplest fix is adding an if_not_found argument: =XLOOKUP(A2, B:B, C:C, "Not found"). If matches should exist but are not being found, the most common causes are: trailing spaces in the lookup value or the lookup array (fix with TRIM()), data type mismatches (text-formatted numbers vs numeric values — fix by wrapping the lookup value in VALUE() or TEXT() as appropriate), and case differences (XLOOKUP is case-insensitive by default, so case differences should not cause issues unless wildcard mode is used).
#VALUE! Errors
A #VALUE! error typically indicates a size mismatch between the lookup_array and return_array. Both must contain the same number of rows (for vertical lookups) or columns (for horizontal lookups). Verify the ranges in the formula and ensure no empty rows or misaligned headers are causing the count to differ.
Slow Performance on Large Tables
XLOOKUP performs well on most business datasets. If calculation is noticeably slow with large tables, enable binary search (search_mode 2 or -2) if your lookup array is sorted. Alternatively, convert the lookup and return ranges to Excel Table references — structured table references often calculate faster than generic range references. Turning off automatic calculation temporarily (Application.Calculation = xlCalculationManual in VBA) is another option when building complex workbooks with many XLOOKUP formulas.
XLOOKUP Returning Wrong Column
If XLOOKUP returns data from the wrong column, verify that the return_array starts at the correct column. A common mistake is accidentally including an extra column at the start of the return range. When returning multiple columns, double-check the column order in the return range matches the order you expect in the output.
XLOOKUP for Horizontal Lookups
XLOOKUP works equally well for horizontal lookups — searching across a row rather than down a column. Where HLOOKUP required specifying a row index number (just as brittle as VLOOKUP’s column index), XLOOKUP simply takes a lookup row and a return row:
=XLOOKUP(B1, MonthHeaders, SalesRow)Where MonthHeaders is a horizontal range of month names and SalesRow is the corresponding row of sales figures. XLOOKUP finds the column matching the month in B1 and returns the sales figure from SalesRow in that same column — no row index number required, no breaking when columns are inserted.
