If you’ve spent any time in Excel, you’ve almost certainly used VLOOKUP and you’ve probably also run into its frustrations. It only searches left to right. It breaks the moment you insert a new column. It defaults to approximate matches unless you remember to turn that off. For years, spreadsheet users either lived with these quirks or graduated to the more powerful, but more complex, INDEX/MATCH combination.
Then came XLOOKUP.
Introduced for Microsoft 365 and Excel 2021, XLOOKUP was built to fix nearly every pain point of its predecessors in a single, more intuitive function. It searches in any direction, handles errors gracefully, and can even return multiple values at once all with a syntax that’s easier to read at a glance.
This article breaks down exactly how XLOOKUP works, from the basic syntax to advanced techniques, so you can start using it with confidence.
XLOOKUP Fundamentals
XLOOKUP’s syntax looks intimidating at first glance, but it’s actually just three required arguments and three optional ones working together:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The three required arguments are the backbone of every XLOOKUP formula:
- lookup_value the value you’re searching for (a cell reference, text, or number)
- lookup_array the range where Excel should look for that value
- return_array the range from which Excel pulls the result once a match is found
With just these three, you already have a fully functional formula that outperforms a basic VLOOKUP.
The three optional arguments unlock XLOOKUP’s real power:
- if_not_found lets you replace the default #N/A error with a custom message, like “No match” or “Check ID”
- match_mode controls whether Excel looks for an exact match, the closest smaller or larger value, or a wildcard pattern
- search_mode determines whether Excel scans from the first row to the last (default) or in reverse, and whether it uses a faster binary search on sorted data
Conceptually, XLOOKUP works by scanning lookup_array for a position that matches lookup_value, then returning the value at that same relative position in return_array. Because the two arrays are matched by position rather than by fixed column offsets, XLOOKUP stays accurate even if you rearrange or insert columns later, a common source of broken formulas in VLOOKUP.
Core Use Cases
XLOOKUP handles a wide range of lookup scenarios, many of which required workarounds or separate functions in the past. Here are the core patterns you’ll use most often.
1. Basic exact-match lookup
The simplest case is finding one value based on another. For example, looking up an employee’s name using their ID:
=XLOOKUP(“E1042”, A2:A100, B2:B100)
2. Left-to-right vs. right-to-left lookups
Unlike VLOOKUP, XLOOKUP doesn’t care about column order. You can search a column on the right and return a value from a column on the left, with no extra setup:
=XLOOKUP(“E1042”, C2:C100, A2:A100)
3. Vertical lookups (column-based)
The most common use case searching down a column of data, just like the earlier examples. This replaces the vast majority of VLOOKUP use cases directly.
4. Horizontal lookups (row-based)
XLOOKUP also replaces HLOOKUP by searching across a row instead of down a column. Simply structure your lookup_array and return_array as rows rather than columns:
=XLOOKUP(“Q3”, B1:E1, B2:E2)
5. Two-way lookups (row and column simultaneously)
By nesting one XLOOKUP inside another, you can match on both a row and a column at once useful for matrix-style tables like a product-by-region sales grid:
=XLOOKUP(“Product A”, A2:A10, XLOOKUP(“Region 2”, B1:E1, B2:E10))
Together, these five patterns cover almost every lookup scenario you’ll encounter in everyday spreadsheet work.
Handling Errors and Missing Data
One of XLOOKUP’s most practical improvements over older functions is how gracefully it handles missing matches. By default, if XLOOKUP can’t find the lookup_value, it returns a #N/A error which can look broken or unprofessional in a shared spreadsheet or dashboard.
The if_not_found argument
Rather than letting that error surface, you can define exactly what should display instead:
=XLOOKUP(“E9999”, A2:A100, B2:B100, “Not found”)
This single addition eliminates the need for wrapping your formula in extra error-handling functions in most cases.
Do you still need IFERROR or IFNA?
Before XLOOKUP, it was standard practice to wrap VLOOKUP in IFERROR() to catch missing matches. With XLOOKUP, this is largely unnecessary for “not found” scenarios since if_not_found handles it directly. However, IFERROR or IFNA can still be useful for catching other error types your formula might produce, such as issues unrelated to a missing match.
Common error types you may still encounter
- #N/A no match found (only if if_not_found isn’t used)
- #VALUE! often caused by mismatched array sizes
- #REF! usually the result of a deleted reference range
- #CALC! occurs with spill-related array errors
Designing user-friendly messages
For dashboards or reports viewed by others, thoughtful if_not_found messages like “Check spelling” or “ID not in database” make spreadsheets far more approachable for non-technical users, reducing confusion and support questions.
Match Modes Explained
The optional match_mode argument gives XLOOKUP flexibility that VLOOKUP never had control over exactly how “close” a match needs to be. It’s the fifth argument in the formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], match_mode)
There are four possible settings:
0 Exact match (default)
If no match_mode is specified, XLOOKUP only returns a result when it finds a precise match. If nothing matches exactly, it returns an error (or your custom if_not_found message).
-1 Exact match, or next smaller item
If there’s no exact match, XLOOKUP falls back to the closest value below the lookup value. This is useful for tiered structures like shipping cost brackets, where a lookup value might fall between two defined thresholds.
1 Exact match, or next larger item
The opposite of -1 if there’s no exact match, it returns the closest value above the lookup value instead.
2 Wildcard match
This enables pattern matching using * (any number of characters) and ? (a single character). It’s especially useful for partial text searches, such as finding a product name when you only know part of it:
=XLOOKUP(“*shirt*”, A2:A50, B2:B50, “No match”, 2)
Choosing the right match mode depends on your data. Exact match is safest for IDs and codes, while the smaller/larger fallback modes are ideal for range-based lookups like tax brackets or grading scales.
Search Modes Explained
The sixth and final argument in XLOOKUP, search_mode, controls the direction and method Excel uses to scan through your lookup_array. It’s often overlooked, but it can be genuinely useful in specific scenarios.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], search_mode)
There are four possible settings:
1 First-to-last search (default)
Excel scans from the top of the range downward and returns the first match it finds. This is the standard behavior most users expect and don’t need to change.
-1 Last-to-first search (reverse lookup)
Excel scans from the bottom of the range upward, returning the last matching value instead of the first. This is particularly useful when your data is logged chronologically and you want the most recent entry for example, finding someone’s latest transaction or status update without having to sort the data first:
=XLOOKUP(“Customer A”, A2:A500, C2:C500, “Not found”, 0, -1)
2 Binary search (ascending order)
Assumes the lookup_array is already sorted in ascending order and uses a faster binary search algorithm. This can meaningfully improve performance on very large datasets, but will return incorrect results if the data isn’t actually sorted.
-2 Binary search (descending order)
The same performance benefit as above, but for data sorted in descending order.
Binary search modes are rarely necessary for everyday spreadsheets, but they’re worth knowing about when working with large, sorted datasets where formula speed matters.
VII. Advanced Techniques
Once you’re comfortable with the basics, XLOOKUP offers several advanced capabilities that go well beyond simple single-value lookups.
Returning multiple columns at once
By widening the return_array to include several columns, XLOOKUP spills all matching values into adjacent cells automatically:
=XLOOKUP(“E1042”, A2:A100, B2:D100)
This single formula can return a name, department, and salary simultaneously with no need for three separate lookups.
Combining XLOOKUP with SUM or AVERAGE
XLOOKUP can dynamically define a range for aggregate functions, which is useful when the exact range isn’t known in advance:
=SUM(XLOOKUP(“Jan”, B1:M1, B2:M10):XLOOKUP(“Mar”, B1:M1, B2:M10))
XLOOKUP + IF for conditional logic
Wrapping XLOOKUP inside an IF statement lets you apply extra business logic to the result, such as flagging values above a threshold or customizing output based on conditions.
Nested XLOOKUP for two-way/matrix lookups
As covered earlier, nesting one XLOOKUP inside another allows matching on both rows and columns ideal for pivot-style tables where you need a value at the intersection of two criteria.
XLOOKUP with data validation dropdowns
Pairing XLOOKUP with a dropdown (via Data Validation) creates interactive lookup tools selecting an item from a dropdown and instantly updates the XLOOKUP result elsewhere on the sheet.
Dynamic named ranges
Combining XLOOKUP with named ranges (especially dynamic ones defined via tables or OFFSET) keeps formulas readable and automatically adjusts as your dataset grows.
These techniques transform XLOOKUP from a simple lookup tool into a foundation for building genuinely dynamic, interactive spreadsheets.
XLOOKUP vs. Other Functions
XLOOKUP was designed to replace several older functions, but understanding exactly how it compares helps you know when it’s the right tool.
XLOOKUP vs. VLOOKUP
VLOOKUP only searches left to right and breaks if columns are inserted or rearranged, since it relies on a fixed column index number. XLOOKUP searches in any direction and references return columns directly, so it stays accurate even when your table structure changes. XLOOKUP also defaults to exact match, while VLOOKUP defaults to approximate match, a common source of VLOOKUP errors for beginners.
XLOOKUP vs. HLOOKUP
HLOOKUP is essentially VLOOKUP’s row-based counterpart, and it shares the same rigidity. XLOOKUP handles horizontal lookups natively, making HLOOKUP largely redundant.
XLOOKUP vs. INDEX/MATCH
INDEX/MATCH has long been the power-user’s alternative to VLOOKUP, offering similar flexibility to XLOOKUP searching in any direction, staying resilient to column changes. However, it requires combining two separate functions and is harder to read at a glance. XLOOKUP achieves the same flexibility with a single, more intuitive function.
XLOOKUP vs. LOOKUP
The older LOOKUP function requires sorted data and offers little error handling. XLOOKUP is more forgiving, more accurate, and doesn’t require pre-sorted arrays for standard exact-match lookups.
When you’d still choose an older function
If you’re working in Excel 2019 or earlier (without XLOOKUP support), or sharing files with users on older versions, INDEX/MATCH remains a reliable fallback that offers similar capabilities.
Performance and Best Practices
Beyond just knowing the syntax, using XLOOKUP effectively means understanding how it behaves on real-world datasets and structuring your spreadsheets to get the most out of it.
Performance on large datasets
For typical spreadsheets with a few thousand rows, XLOOKUP’s default search mode performs perfectly well. On much larger datasets, tens of thousands of rows or more switching to binary search mode (2 or -2) can noticeably speed up calculation, but only if your data is genuinely sorted. Using binary search on unsorted data will silently return incorrect results, so this optimization should be used carefully.
Structuring data for reliable lookups
XLOOKUP works best when your lookup_array values are unique. If duplicates exist, XLOOKUP returns only the first (or last, in reverse mode) match, which can silently produce misleading results. Cleaning up duplicate entries before building lookup formulas helps avoid this.
Using structured references and Excel Tables
Converting your data into an official Excel Table (Ctrl+T) lets you reference columns by name instead of cell range, such as Employees[Name] instead of B2:B100. This makes formulas more readable and automatically expands the range as new rows are added, which is especially valuable for growing datasets.
Absolute vs. relative referencing
When copying an XLOOKUP formula across multiple cells, lock your lookup_array and return_array with absolute references ($A$2:$A$100) to prevent them from shifting, while allowing the lookup_value to remain relative so it updates per row.
Compatibility and Limitations
Before relying heavily on XLOOKUP, it’s important to understand where it works and where it doesn’t since compatibility issues can cause real problems when sharing files.
Which Excel versions support XLOOKUP
XLOOKUP is available in Microsoft 365 (with an active subscription), Excel 2021, and Excel for the web. It is not available in Excel 2019, Excel 2016, or earlier perpetual-license versions, which only support VLOOKUP, HLOOKUP, and INDEX/MATCH.
Google Sheets support
Google Sheets also supports XLOOKUP, with largely the same syntax and argument order as Excel. Minor differences can occasionally appear in how certain edge cases (like empty ranges) are handled, so it’s worth testing formulas after migrating between platforms.
Opening XLOOKUP files in older Excel versions
If a workbook containing XLOOKUP formulas is opened in a version that doesn’t support the function, Excel will display a #NAME? error instead of the calculated result. The formula itself isn’t deleted, but it becomes unusable until reopened in a compatible version, an important consideration if you’re sharing files with clients or colleagues on older software.
Known limitations
XLOOKUP cannot look across multiple non-contiguous ranges in a single call, and it doesn’t natively support looking up values across multiple sheets without additional formula structure. For teams still standardized on Excel 2019 or earlier, INDEX/MATCH remains the safer, more universally compatible choice despite being less intuitive to write.
Common Mistakes and Troubleshooting
Even though XLOOKUP is more forgiving than older lookup functions, a few recurring mistakes can still trip up new users. Knowing what to watch for makes troubleshooting much faster.
Mismatched array sizes
The lookup_array and return_array must contain the same number of rows (or columns, for horizontal lookups). If they don’t match, XLOOKUP returns a #VALUE! error. This often happens when one range is accidentally extended or shortened during editing.
Missing quotation marks around text values
Text values used directly in a formula must be wrapped in quotes: “E1042”, not E1042. Without quotes, Excel interprets the text as a name or reference, which usually triggers an error.
Circular reference issues
If your return_array accidentally includes the cell where the formula itself lives, Excel will flag a circular reference. This is easy to overlook in large, interconnected spreadsheets.
#SPILL! errors
When XLOOKUP is set up to return multiple values (a spill array) but the cells below or beside it already contain data, Excel can’t place the results and shows a #SPILL! error instead. Clearing the blocking cells resolves this.
Debugging with built-in tools
Excel’s Evaluate Formula tool (Formulas tab) steps through a formula calculation piece by piece, making it easier to spot where things break. Alternatively, selecting part of a formula in the formula bar and pressing F9 shows the calculated value of just that section, a fast way to isolate errors without rewriting the whole formula.
Real-World Examples and Case Studies
Seeing XLOOKUP applied to practical, everyday scenarios makes it easier to recognize where it fits into your own spreadsheets.
Employee database lookup
A common HR use case: given an employee ID, pull their name, department, and manager from a master list.
=XLOOKUP(A2, Employees[ID], Employees[Name], “ID not found”)
Using a Table reference here (Employees[ID]) keeps the formula readable and automatically adjusts as new employees are added.
Inventory and price list lookup
Retail and inventory spreadsheets often need to pull a product’s price based on a SKU, especially when the price list is on a separate sheet:
=XLOOKUP(A2, PriceList!A:A, PriceList!C:C, “SKU not found”)
This pattern is especially useful when order forms and price lists are maintained separately but need to stay linked.
Grading or tiered commission structures
Using approximate match mode (-1), XLOOKUP can assign a grade or commission tier based on a score or sales total falling within a range:
=XLOOKUP(B2, Thresholds!A:A, Thresholds!B:B, “N/A”, -1)
This avoids the need for long nested IF statements to handle multiple tiers.
Two-way matrix lookup
For a sales table organized by product (rows) and region (columns), a nested XLOOKUP finds the exact intersection value:
=XLOOKUP(A2, SalesData!A:A, XLOOKUP(B2, SalesData!1:1, SalesData!2:100))
This is far more intuitive than the equivalent INDEX/MATCH formula and adapts automatically if rows or columns are reordered.
Each of these examples reflects a genuinely common spreadsheet task the kind XLOOKUP was designed to simplify.
Quick Reference / Cheat Sheet
For quick recall, here’s a condensed summary of everything covered so far useful to bookmark or keep open while building your own formulas.
Full syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Match mode quick reference
| Value | Behavior |
| 0 (default) | Exact match only |
| -1 | Exact match, or next smaller item |
| 1 | Exact match, or next larger item |
| 2 | Wildcard match (*, ?) |
Search mode quick reference
| Value | Behavior |
| 1 (default) | First to last |
| -1 | Last to first (reverse lookup) |
| 2 | Binary search, ascending sorted data |
| -2 | Binary search, descending sorted data |
Common formula templates
Basic lookup:
=XLOOKUP(A2, B:B, C:C)
Lookup with custom error message:
=XLOOKUP(A2, B:B, C:C, “Not found”)
Reverse lookup (most recent match):
=XLOOKUP(A2, B:B, C:C, “Not found”, 0, -1)
Approximate match (tiered ranges):
=XLOOKUP(A2, B:B, C:C, “N/A”, -1)
Two-way matrix lookup:
=XLOOKUP(A2, B:B, XLOOKUP(C2, D1:Z1, D2:Z100))
Multi-column return:
=XLOOKUP(A2, B:B, C:E)
Quick decision guide
- Need an exact match? Leave match_mode blank.
- Need the most recent entry in a log? Use search_mode = -1.
- Working with tax brackets or tiers? Use match_mode = -1 or 1.
- Searching for partial text? Use match_mode = 2 with wildcards.
Keep this section handy as a reference while writing your own formulas; it covers the vast majority of everyday XLOOKUP needs.
Conclusion
XLOOKUP represents one of the most meaningful upgrades Excel has introduced in recent years, and once you’ve worked through its syntax, match modes, and error handling, it’s easy to see why it has largely replaced VLOOKUP for everyday use. It solves real, longstanding frustrations, directional limitations, fragile column references, and unhelpful error messages with a single function that’s genuinely easier to read and maintain.
Migrating from VLOOKUP
If you’re used to VLOOKUP, the transition is straightforward. Start by replacing simple lookups with the three-argument version of XLOOKUP, then gradually incorporate if_not_found for cleaner error handling. From there, experimenting with match modes and reverse search will show you just how much more flexible XLOOKUP is for scenarios VLOOKUP could never handle cleanly.
Where to go next
Once XLOOKUP feels comfortable, a few related skills are worth exploring:
- Dynamic arrays (like FILTER, SORT, and UNIQUE) pair naturally with XLOOKUP and unlock even more flexible, formula-driven spreadsheets.
- Power Query for larger, messier datasets where lookups alone aren’t enough to clean and merge data effectively.
- Excel Tables structuring your data properly makes every lookup formula, including XLOOKUP, more reliable and easier to maintain long-term.
Whether you’re building a simple employee directory or a complex, multi-sheet reporting dashboard, XLOOKUP is very likely to become one of the most-used functions in your spreadsheet toolkit. Mastering it now will save considerable time and formula headaches down the road.
