- 🔎 XLOOKUP searches one range and returns the corresponding value from another, with exact matching as the default in current Excel and Google Sheets documentation.
- 🧩 Its three required inputs are the value to find, the lookup range, and the return range; optional arguments control missing values, match behavior, and search direction.
- đź“Š The strongest practical advantage over VLOOKUP is structural: the return range can sit to the left or right, and inserted columns do not change a hard-coded return-column number.
- ⚡ Investigative finding: binary-search modes can improve search efficiency on sorted data, but Microsoft explicitly warns that unsorted arrays can produce invalid results.
- 🔄 Reverse search makes last-match retrieval a native formula pattern, while wildcard mode supports partial-text matching without adding helper functions.
- 🎯 For most modern workbooks, use the function for transparent, reproducible lookups, but preserve INDEX/MATCH or legacy formulas when backward compatibility is a hard requirement.
XLOOKUP is the modern lookup function built to remove three traps that made older spreadsheet lookups fragile: one-way searching, hard-coded return columns, and approximate matching that could be triggered by omission. Microsoft now documents exact match as the default, while the return range can sit on either side of the search range. That combination turns a common spreadsheet task into a more explicit formula instead of a workaround-heavy one (Microsoft, n.d.).
The function matters because lookups sit at the center of practical spreadsheet work. Product IDs need prices, employee numbers need departments, invoice codes need status values, and analysis models need one table to pull context from another. Our broader spreadsheet formulas guide explains how formulas act as the logic layer of Excel and Google Sheets; this guide narrows that idea to the lookup pattern most users now need to understand.
The syntax is simple enough to learn quickly, but the optional arguments are where reliability improves. A custom not-found message can replace raw #N/A errors. Search direction can retrieve the first or last match. Approximate modes can map values into thresholds. Wildcards can find partial text. A single result can also spill multiple return columns. The trade-off is that these controls must be used deliberately, especially when duplicate keys, unsorted data, hidden spaces, or older Excel versions are involved.
How XLOOKUP Works
Microsoft defines the function as a search across a range or array that returns the item corresponding to the first match it finds. Its Excel syntax is shown below (Microsoft, n.d.).
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The six arguments in plain language
Only the first three arguments are required. The last three control what happens when a match fails, what kind of match is allowed, and where the search begins.
| Argument | What it controls | Practical use |
| lookup_value | The value or cell to find | A product ID such as P102 or a reference such as D2 |
| lookup_array | The single row or column to search | Product IDs in A2:A100 |
| return_array | The row, column, or multi-column range to return from | Prices in B2:B100 or details in B2:D100 |
| if_not_found | A custom result when no valid match exists | Return “Not found” instead of #N/A |
| match_mode | Exact, next smaller, next larger, or wildcard matching | Threshold tables or partial-text searches |
| search_mode | First-to-last, last-to-first, or binary search | Retrieve the latest duplicate or search sorted arrays |
Start with the basic exact-match pattern
Suppose column A contains Product ID and column B contains Price. The rows are P101 / 1200, P102 / 1850, and P103 / 999. To return the price for P102, use:
=XLOOKUP(“P102”, A2:A4, B2:B4)
Use a cell reference and a friendly fallback
In a working sheet, hard-coding the search value is usually less useful than pointing at an input cell. If D2 contains the product ID, the formula becomes:
=XLOOKUP(D2, A2:A4, B2:B4, “Not found”)
Why This Lookup Pattern Is Safer Than VLOOKUP
Microsoft describes XLOOKUP as an improved successor to VLOOKUP and recommends it over both VLOOKUP and HLOOKUP for supported versions. Joe McDaid, then a Program Manager on the Excel team, wrote that Microsoft “strongly recommend[s] using XLOOKUP in favor of VLOOKUP and HLOOKUP” because the newer function is simpler and avoids several older limitations (McDaid, 2019).
The biggest gain is not shorter syntax. It is lower structural risk. VLOOKUP searches the first column of a table array and returns a column by index number. If a model says return column 4, that number depends on the table shape. XLOOKUP separates the search range from the return range, so the intended columns are named directly in the formula. That is easier to audit when a worksheet changes.
| Feature | XLOOKUP | VLOOKUP |
| Default match | Exact | Approximate if range_lookup is omitted |
| Lookup direction | Left or right | Search key must be in the first table column |
| Return definition | Direct return range | Column index number |
| Last-match search | Built in with search_mode -1 | Requires a different pattern |
| Wildcard match | Built in with match_mode 2 | Possible with exact-match mode and wildcards |
| Multiple return columns | Can return an array from a multi-column range | Normally one indexed column per formula |
| Older Excel compatibility | Not available in Excel 2016 or 2019 | Widely supported in older versions |
Six Practical Patterns That Cover Most Real Work
The following patterns use the optional arguments to solve common lookup problems without adding helper columns or deeply nested formulas.
1. Return columns to the left
Because the search range and return range are independent, the result can sit anywhere. If employee IDs are in column D and names are in column B, this works without rearranging the table:
=XLOOKUP(F2, D2:D100, B2:B100, “Not found”)
2. Return several columns at once
A multi-column return range can spill several fields from one match. If B:D holds product name, category, and price, one formula can return all three:
=XLOOKUP(F2, A2:A100, B2:D100, “Not found”)
3. Retrieve the last matching record
Duplicate keys are common in transaction logs, status histories, and repeated customer activity. Setting search_mode to -1 searches from the last item backward, which makes last-match retrieval explicit:
=XLOOKUP(F2, A2:A100, B2:B100, “Not found”, 0, -1)
4. Match the next smaller threshold
Approximate matching is useful when exact keys do not exist, such as commission bands, grading thresholds, or quantity discounts. A match_mode of -1 asks for an exact match or the next smaller item:
=XLOOKUP(F2, A2:A10, B2:B10, “Not found”, -1)
5. Find partial text with wildcards
Wildcard mode uses *, ?, and ~ with match_mode 2. A search for text containing the word phone can be written as:
=XLOOKUP(“*phone*”, A2:A100, B2:B100, “Not found”, 2)
6. Build a two-way lookup
Nested formulas can match a row label and a column header. Microsoft documents this pattern as a way to find an intersection, similar in purpose to INDEX with two MATCH operations. A compact version is:
=XLOOKUP(H2, B2:B20, XLOOKUP(H3, C1:G1, C2:G20))
The Risks That Matter More Than Syntax
Most lookup failures are not caused by the function itself. They come from the data model around it. The same formula can be correct and still return an unhelpful answer if keys are duplicated, text is inconsistent, or the chosen match mode does not fit the business rule.
Duplicate keys can turn “correct” into ambiguous
By default, the formula returns the first match it finds. That is predictable, but it may not be the business answer a user intended. If a customer ID appears five times, “first match” and “latest status” are different questions. Before choosing search_mode -1 as a shortcut, confirm what duplicates mean. The site’s guide to highlighting duplicates in Excel is useful when the first task is to diagnose repeated keys rather than hide them inside lookup logic.
Binary search is an optimization with a correctness condition
Microsoft supports search_mode 2 for ascending binary search and -2 for descending binary search. The documentation also warns that the lookup array must be sorted in the required direction; otherwise invalid results can be returned (Microsoft, n.d.). That makes binary mode a specialized choice, not a universal performance switch.
The practical rule is simple: use first-to-last or last-to-first search unless the dataset is deliberately sorted and the sorting requirement is controlled. A fast answer that can silently become wrong is not an optimization.
Messy text breaks exact matches quietly
A product code that looks identical on screen may contain leading spaces, trailing spaces, non-printing characters, or inconsistent number-versus-text storage. A lookup cannot infer that two differently stored keys should be treated as the same. Clean the source before expanding the formula. The Bloomberg Excel data-cleaning guide covers the broader discipline of dealing with blanks, #N/A values, and inconsistent spreadsheet inputs before analysis.
Compatibility still decides which formula belongs in a shared workbook
Current Microsoft support documentation says the function is not available in Excel 2016 or Excel 2019, even though those versions may display a workbook created in a newer edition. For teams sharing files with older desktop installs, INDEX/MATCH or VLOOKUP may remain the safer deployment choice. Formula quality includes whether colleagues can calculate the workbook, not only whether the formula is elegant on the author’s machine (Microsoft, n.d.).
A Decision Table for Match and Search Modes
The optional modes are easiest to choose by starting with the business question, not the numeric code.
| Need | Recommended setting | Main risk to check |
| Exact ID or code match | match_mode 0 | Hidden spaces, text/number mismatch |
| Nearest lower threshold | match_mode -1 | Threshold logic must match the policy |
| Nearest higher threshold | match_mode 1 | Boundary values need testing |
| Partial text | match_mode 2 | Wildcards may match more rows than expected |
| Latest duplicate | search_mode -1 | Confirm that “last row” really means latest |
| Large sorted array with binary search | search_mode 2 or -2 | Wrong sort order can return invalid results |
Excel and Google Sheets Now Share the Core Model
Google Sheets documents the same basic structure: a search key, a lookup range, a result range, an optional missing value, match mode, and search mode. Its current help page also lists exact matching as the default, reverse search with -1, wildcard matching with 2, and binary search modes that require correctly sorted ranges (Google, n.d.).
That cross-platform alignment is useful for teams moving between Microsoft 365 and Google Workspace. A formula such as =XLOOKUP(A2, D2:D100, B2:B100, “Not found”) is conceptually portable. Still, workbooks and Sheets models should be tested after migration because surrounding features, structured references, dynamic arrays, locale separators, named ranges, and other formulas can behave differently even when the lookup itself is familiar.
When INDEX, MATCH, or XMATCH Still Makes Sense
A modern lookup function is not automatically the right answer for every model. INDEX with MATCH remains valuable when a workbook must run in older Excel versions, when a team has established audited patterns around it, or when a formula needs to return a reference for another operation. XMATCH is also useful when only the position of an item is needed rather than the item itself.
The decision should follow maintainability and compatibility. For a new Microsoft 365 workbook, the newer lookup usually produces clearer intent. For a long-lived financial model distributed to mixed Excel versions, replacing every legacy lookup just to modernize syntax can create deployment risk without adding business value.
The Future of XLOOKUP in 2027
The most credible 2027 change is not that deterministic lookup formulas disappear. It is that AI assistance increasingly helps users write, explain, and audit them. Microsoft already documents Copilot formula suggestions that can complete formulas after a user types an equals sign, using nearby headers, cells, tables, and existing formulas as context. In June 2026, Microsoft also described Copilot in Excel workflows that can plan changes, identify affected ranges and formulas, and leave edits traceable in the Show Changes pane (Microsoft, 2026).
That direction strengthens the role of reliable native formulas. Microsoft’s own COPILOT function documentation explicitly advises users to use native formulas for numerical calculations and XLOOKUP for workbook lookups rather than asking an AI model to perform deterministic retrieval. In other words, AI is becoming a layer around spreadsheet logic, not a replacement for exact spreadsheet logic.
Teams exploring that shift can pair formula literacy with our guide to using AI to analyse data, which emphasizes validation and reproducibility, and the site’s Microsoft Copilot review, which looks at the broader Office workflow. The likely 2027 workflow is hybrid: people describe the result they need in natural language, AI proposes or explains a formula, and the workbook still stores an inspectable formula that recalculates predictably. The uncertainty is product packaging and interface design, not the need for deterministic retrieval.
Takeaways
- Use exact matching as the default for IDs, codes, names, and other keys that should not drift to a nearby value.
- Separate lookup and return ranges to make models easier to audit and less dependent on fixed column positions.
- Use search_mode -1 only when the final occurrence has a clear business meaning, such as the latest record in a properly ordered log.
- Treat approximate and wildcard matching as business rules that deserve boundary tests, not as shortcuts.
- Avoid binary-search modes unless the sort order is guaranteed and controlled.
- Clean keys before debugging the formula when visually identical values fail to match.
- Keep compatibility requirements in view when files must work in Excel 2016 or 2019.
Conclusion
XLOOKUP succeeds because it makes the intent of a lookup easier to see. The search range is explicit. The return range is explicit. Exact matching is the default. Reverse search, wildcard matching, custom missing values, and multi-column returns are available without rebuilding the worksheet around the formula.
That does not remove the need for judgment. Duplicate keys still need a policy. Approximate thresholds still need validation. Binary search still depends on sorted data. Cross-platform work still needs testing, and older Excel versions can still determine whether a modern formula belongs in a shared model.
For new work in supported Excel versions and Google Sheets, the function is a strong default because it balances readability with capability. Its best use is not to make formulas clever. It is to make the relationship between a question, a key, and the returned data transparent enough that another person can audit it later.
Frequently Asked Questions
What is XLOOKUP used for?
It searches one row or column for a value and returns the corresponding value from another row, column, or multi-column range. Common uses include retrieving prices by product ID, departments by employee ID, status values by order number, and threshold-based rates.
What is the basic XLOOKUP formula?
The basic pattern is =XLOOKUP(lookup_value, lookup_array, return_array). The first argument is what you want to find, the second is where Excel or Sheets should search, and the third is where the result should come from.
Is XLOOKUP better than VLOOKUP?
For supported versions, it is usually more flexible because it can return values from either side of the search column, defaults to exact matching, supports reverse search, and does not require a numeric return-column index. VLOOKUP still matters for older Excel compatibility.
Can XLOOKUP return multiple columns?
Yes. In modern Excel, the return_array can span several columns, and the matching row can spill those values into adjacent cells. Microsoft documents this behavior directly. Google Sheets also notes that a multi-column result range can return the corresponding row or column at the matched position.
How do I return the last matching value?
Set match_mode to 0 for exact matching and search_mode to -1. For example: =XLOOKUP(D2,A2:A100,B2:B100,”Not found”,0,-1). This searches from the bottom upward and returns the last matching entry.
Why does an exact lookup return #N/A when the value looks identical?
The key may contain leading or trailing spaces, non-printing characters, or a number stored as text. Check the source data type and clean the key before changing match modes. Also confirm that the lookup and return ranges align to the same row count.
Does Google Sheets support XLOOKUP?
Yes. Google’s current Docs Editors Help documents XLOOKUP with optional missing-value, match-mode, and search-mode arguments, including exact, approximate, wildcard, reverse, and binary-search behavior (Google, n.d.).
Methodology
This guide was researched against current Microsoft Support documentation for XLOOKUP syntax, match modes, search modes, multi-column returns, and version compatibility; Google Docs Editors Help for Google Sheets behavior; Microsoft Tech Community material from Joe McDaid for the function’s design rationale; and Microsoft’s 2026 Excel and Copilot documentation for forward-looking workflow context. Internal links were selected only from live, indexed Perplexityaimagazine.com pages verified during research.
No claim in this article depends on an unverified third-party benchmark, adoption estimate, or fabricated product test. Worked formulas are instructional examples derived from documented behavior. A limitation is that spreadsheet interfaces and Copilot packaging can change, so feature availability should be checked against current vendor documentation before deployment.
This article was drafted with AI assistance and reviewed by the Perplexity AI Editorial Team. All data, citations, and claims have been independently verified against primary sources.
References
Google. (n.d.). XLOOKUP function. Google Docs Editors Help.
McDaid, J. (2019, August 28). Announcing XLOOKUP. Microsoft Tech Community.
Microsoft. (n.d.). XLOOKUP function. Microsoft Support.
Microsoft. (n.d.). Turn Copilot formula suggestions on or off in Excel. Microsoft Support.
