XLOOKUP Excel: Practical Guide to Modern Lookups
Master XLOOKUP in Excel with practical examples, tips, and best practices. Learn syntax, edge cases, and how it compares to VLOOKUP for faster, more reliable lookups.
By the end of this guide you'll master XLOOKUP in Excel, using it to retrieve values from a table with exact or approximate matches, reverse lookups, and multiple return options. You'll learn the syntax, common pitfalls, and practical examples you can adapt to real datasets. This tutorial assumes Excel 365 or Excel 2021 with XLOOKUP available.
Why XLOOKUP matters
According to XLS Library, XLOOKUP is a game-changing function in Excel that replaces older lookups with a more flexible and robust approach. It supports vertical and horizontal lookups, exact and approximate matches, and direct array returns. For dashboards and data models, XLOOKUP reduces formula clutter and improves readability. The XLS Library team found that practitioners see faster development cycles and fewer errors when adopting XLOOKUP over legacy functions like VLOOKUP and HLOOKUP. This section explores why you should care about this function and how it fits into modern Excel workflows.
Core syntax and arguments
XLOOKUP has a clean, six-argument structure (three required, three optional):
- lookup_value: what you want to find
- lookup_array: where to search
- return_array: where to pull results from
- [if_not_found]: value if nothing is found (optional)
- [match_mode]: 0 exact, -1 exact or next smaller, 1 exact or next larger, -2 wildcards (optional)
- [search_mode]: 1 search from first to last, -1 search from last to first (optional)
Common defaults are exact matches (match_mode = 0) and standard left-to-right searches (search_mode = 1). A single short formula can replace complex INDEX/MATCH chains, making formulas easier to read and audit. For more guidance, refer to the XLS Library's practical examples.
Basic vertical lookup example
Suppose you have a product table with IDs in A2:A10 and prices in B2:B10. To fetch the price for a given product code in D2, use:
=XLOOKUP(D2, A2:A10, B2:B10, "Not found", 0, 1)
This returns the matching price or a helpful message if the product code is missing. If the product list grows, XLOOKUP continues to work efficiently without needing to adjust column indices as with VLOOKUP. The example demonstrates exact matching and a custom not-found message to improve user experience.
Horizontal lookups with XLOOKUP
XLOOKUP also supports horizontal lookups by treating row headers as the lookup_array and the data row as the return_array. For example, to locate a metric in the header row and pull the corresponding value from a data row beneath it, you can swap the arrays:
=XLOOKUP("Q2", 1:1, 2:2)
This flexibility lets you build cross-tab reports without creating separate helper formulas for transposed data. Use descriptive headers and ensure alignment between lookup_array and return_array to avoid misreads.
Handling missing values and errors
A common pitfall is returning #N/A when a lookup fails. Use the optional [if_not_found] argument to provide a friendly fallback. You can also combine XLOOKUP with IFERROR for broader safety in large workbooks:
=XLOOKUP(D2, A2:A10, B2:B10, "N/A", 0, 1)
Choosing an appropriate [if_not_found] value improves user experience and downstream analysis. If your data contains inconsistent data types, normalize the lookup_value and lookup_array before applying XLOOKUP to prevent subtle mismatches.
XLOOKUP with multiple return values
XLOOKUP can return multiple values as a spill when you provide a multi-column return_array, enabling dynamic arrays. For instance, if you want both price and stock level together, select a 2D range for return_array:
=XLOOKUP(D2, A2:A10, B2:C10)
This spills into two adjacent columns, giving you synchronized results without writing separate formulas. Ensure your Excel version supports dynamic arrays (Excel 365/2021+).
When to use XLOOKUP vs INDEX/MATCH vs VLOOKUP
XLOOKUP consolidates several scenarios into a simpler formula. Compared with VLOOKUP, it does not require column-index guessing and can look left or up, not just to the right. INDEX/MATCH remains powerful for very old Excel versions or when you need extensive customization, but XLOOKUP generally provides cleaner syntax and faster development. In dashboards, using XLOOKUP reduces maintenance costs and improves clarity for colleagues.
Performance considerations and best practices
When working with large datasets, avoid searching entire columns if you know your data boundaries. Restrict lookup_array and return_array to the actual data range to reduce calculation overhead. Prefer exact-match mode (0) unless you have a specific reason for approximate lookups. Keep not-found messages concise and consistent, and document the logic behind [match_mode] choices for future reviewers.
Common troubleshooting and best practices
If XLOOKUP returns unexpected results, verify the data types in both lookup_value and lookup_array (text vs numbers). Use helper columns to preprocess data when necessary. Always test edge cases: missing values, duplicates in the lookup_array, and lookups at the end of a list. Finally, maintain a small set of tested samples to validate updates before applying formulas across your workbook.
Tools & Materials
- Excel 365 or Excel 2021(XLOOKUP availability varies by build; ensure you are on a modern subscription or perpetual license that includes the function.)
- Sample data workbook(Include at least one lookup column, a return column, and a test lookup value.)
- Computer with internet access(Needed to reference online examples and verify syntax when learning.)
- Text editor or notes app(Optional for jotting syntax and edge cases.)
Steps
Estimated time: 25-40 minutes
- 1
Prepare your data
Open your workbook and locate the table you will query. Ensure the lookup column is well-structured (no mixed data types) and that the return column aligns with the data you want to retrieve. This preparation prevents mismatches and simplifies formula construction.
Tip: Clean data first: trim spaces, unify data types, and remove duplicates in the lookup column. - 2
Draft the basic XLOOKUP formula
In a destination cell, start with the core syntax: =XLOOKUP(lookup_value, lookup_array, return_array). This establishes the core lookup without extra bells and whistles. Consider where your lookup_value will come from (a cell or a constant).
Tip: Use named ranges to make formulas easier to read and maintain. - 3
Add an optional not-found message
Include the [if_not_found] argument to handle missing lookups gracefully. This helps end users understand when a value isn’t present instead of showing a generic error.
Tip: Keep messages concise and consistent across your workbook. - 4
Tweak match and search modes
If you need nearest match behavior, set [match_mode] to -1 or 1. For speed and predictability, keep [search_mode] as 1 (start to end) unless you specifically need reverse order.
Tip: Document the chosen modes so future editors understand the rationale. - 5
Test with real data
Run the formula with several known values, including edge cases like missing entries and duplicates. Confirm that the results align with expectations across all scenarios.
Tip: Create a small test sheet that mirrors production data for safe validation. - 6
Copy and adapt
Drag or copy the formula to adjacent cells if you need a column of results. Ensure the relative references behave as intended when filling down.
Tip: Consider using dynamic named ranges if your data grows over time. - 7
Experiment with multi-return arrays
If your data has multiple columns to return, use a multi-column return_array to spill results across columns. This can replace several separate formulas.
Tip: Ensure your version supports dynamic arrays for correct spilling. - 8
Review and document
Add comments or a documentation note describing what the formula does, why you chose specific match modes, and how errors are handled.
Tip: Documentation reduces future maintenance effort and user confusion.
People Also Ask
What is XLOOKUP in Excel?
XLOOKUP is a flexible lookup function that replaces older VLOOKUP/HLOOKUP. It supports vertical and horizontal lookups, exact and approximate matching, and can return single values or arrays. It simplifies many common lookup scenarios.
XLOOKUP is a flexible replacement for older lookup functions in Excel that can search in any direction and return values or arrays.
How do I perform a vertical lookup with XLOOKUP?
Use XLOOKUP with a vertical lookup by setting the lookup_array to the column you will search and the return_array to the column with the values you want to return. Include a not-found message to handle missing data gracefully.
For a vertical lookup, point the search column to the left of the results and specify what to return when there is no match.
Can XLOOKUP look to the left or up?
Yes. Unlike VLOOKUP, XLOOKUP can search in any direction by letting you choose the lookup and return arrays without being limited to left-to-right configurations.
Yes, XLOOKUP can look in any direction, making it more flexible than older lookup functions.
What happens if there are duplicates in the lookup column?
XLOOKUP returns the first match found according to the search_mode. To handle duplicates, you may need additional logic or unique keys to ensure consistent results.
If duplicates exist, XLOOKUP returns the first matching result based on the search order.
Is XLOOKUP available on all Excel versions?
XLOOKUP is available in Excel 365 and Excel 2021+. Older versions may not support it without updates or alternative formulas like INDEX/MATCH.
XLOOKUP is available in newer Excel versions; older ones use alternatives.
Watch Video
The Essentials
- Master XLOOKUP syntax and options
- Use if_not_found for graceful handling
- XLOOKUP can return arrays for multi-column results
- Choose exact match and default to simple search modes
- Test across edge cases before scaling formulas
