How to Use XLOOKUP in Excel: Complete Guide with Examples, Syntax, and Real-World Use Cases
Learn how to use XLOOKUP in Excel with syntax, examples, and tips. Replace VLOOKUP, search left or right, handle errors, and master modern lookups fast.

Learning how to use XLOOKUP in Excel is one of the most valuable upgrades a spreadsheet user can make in 2026, because this single function replaces the aging VLOOKUP, HLOOKUP, and even nested INDEX/MATCH combinations that have frustrated analysts for decades. XLOOKUP, introduced in Microsoft 365 and Excel 2021, brings a cleaner syntax, native error handling, bidirectional searching, and exact-match defaults that prevent the silent data corruption common with older lookup functions. If you work with reports, dashboards, or finance models, XLOOKUP will save hours every week.
The reason XLOOKUP matters so much is that the old vlookup excel function had structural limitations: it could only search the leftmost column, returned errors when columns were rearranged, and required a fourth argument that defaulted to approximate match, producing wrong answers when users forgot to type FALSE. XLOOKUP solves all of these issues with a syntax that mirrors how people actually think about lookups: tell Excel what to find, where to look for it, and where to return the matching value. The function returns clean results without rigid column-number dependencies.
Throughout this guide you will learn the full XLOOKUP syntax, watch worked examples for common business tasks, compare it directly against VLOOKUP and INDEX/MATCH, and pick up advanced techniques like two-way lookups, wildcard searching, and reverse searching. You will also see how XLOOKUP handles arrays, returns multiple columns at once, and integrates beautifully with dynamic array functions such as FILTER, SORT, and UNIQUE. By the end you will be ready to retire VLOOKUP from your daily workflow forever.
For context, consider how analysts at Fortune 500 firms now treat XLOOKUP as the default lookup function in new workbooks. A 2025 Microsoft survey of advanced Excel users found that 78 percent had migrated away from VLOOKUP within twelve months of adopting XLOOKUP, citing accuracy and speed as the leading reasons. Even for casual users building household budgets or small business inventory sheets, the function reduces typos and broken formulas, which is exactly why I recommend it as the foundation of any modern Excel skillset today.
Before diving deep, it helps to understand the broader Excel ecosystem XLOOKUP lives within. Mastering XLOOKUP unlocks downstream wins across PivotTables, Power Query refreshes, and even charts that depend on dynamic source ranges that change daily.
This guide is structured for both beginners and intermediate users. If you have never written a lookup formula, start with the syntax walkthrough and the basic example, then work through the tabs section showing common scenarios. If you already know VLOOKUP, jump to the comparison section to see exactly which arguments map across, then explore the advanced patterns like nested XLOOKUPs and array-return tricks that genuinely cannot be replicated easily in older versions of the function.
Finally, remember that XLOOKUP is only available in Microsoft 365, Excel 2021, Excel 2024, and Excel for the Web. If you share workbooks with users on Excel 2019 or earlier, your XLOOKUP formulas will display as #NAME? errors for them. The compatibility section later in this guide explains workarounds, including converting XLOOKUP back to INDEX/MATCH for legacy distribution. Now let us get into the actual syntax and the first practical example you can copy into your workbook within sixty seconds.
Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1โ1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
XLOOKUP by the Numbers

XLOOKUP Syntax Breakdown: All Six Arguments Explained
lookup_value
lookup_array
return_array
if_not_found
match_mode
search_mode
Let us walk through a real worked example so you can see XLOOKUP in action. Imagine you have a table in cells A2 to C100 containing employee IDs in column A, names in column B, and salaries in column C. To find the name belonging to employee ID 1042, you would write =XLOOKUP(1042, A2:A100, B2:B100). That is it. Three arguments, plain English logic, no column index numbers to count, and no risk of approximate-match disasters that plague legacy VLOOKUP users every single day.
Now extend the example. Suppose the ID does not exist in your data. Without an if_not_found argument, XLOOKUP returns the standard #N/A error. To fix that gracefully, write =XLOOKUP(1042, A2:A100, B2:B100, "Employee Not Found"). The fourth argument shows clean text rather than an ugly error code. This is one of the biggest day-to-day improvements over VLOOKUP, where you had to wrap the entire formula in IFERROR or IFNA, doubling the length and making maintenance painful for new analysts.
Next, try a reverse lookup. Suppose you have transaction logs where each customer appears multiple times and you want the most recent transaction date. Use =XLOOKUP(customerID, A2:A1000, D2:D1000, , 0, -1). The -1 in the search_mode argument tells Excel to scan from the bottom upward, returning the last match first. This single feature replaces complex array formulas that previously required LARGE, MAX, or array-entered INDEX/MATCH combinations to achieve the same result.
Wildcard matching is another superpower. If you want to find any product whose name contains the word laptop, write =XLOOKUP("*laptop*", ProductNames, Prices, , 2). The match_mode of 2 activates wildcard support. The asterisk matches any number of characters, and a question mark matches a single character. This is especially powerful when joined with text in cells, like =XLOOKUP("*"&A1&"*", ProductNames, Prices, "None", 2), which builds the search pattern dynamically from a search box in your sheet.
A favorite advanced trick is returning multiple columns at once. If your return_array spans several adjacent columns, XLOOKUP will spill all of them into the result. For example =XLOOKUP(1042, A2:A100, B2:D100) returns the name, department, and salary simultaneously across three cells. This eliminates the need to write three separate lookup formulas, saving keystrokes and ensuring all three values come from the same matched row, which improves data integrity in dashboards used for executive reporting.
Two-way lookups are equally elegant. Suppose you have a matrix with regions down column A and products across row 1, with sales figures in the body. To find sales for the West region and Product B, nest two XLOOKUPs: =XLOOKUP("West", A2:A10, XLOOKUP("Product B", B1:F1, B2:F10)). The inner XLOOKUP returns the entire column for Product B, and the outer XLOOKUP picks the West row from that column. Many users describe this pattern as cleaner than the equivalent INDEX/MATCH/MATCH nest.
Combining XLOOKUP with data validation creates polished interactive tools. Pair it with a drop-down list referencing your lookup column, and instantly any selection returns the related data. If you are not yet using validation lists, the tutorial on institute of creative excellence filter techniques will complement your lookup work, because filtering and looking up are the two most common ways analysts isolate the rows they actually care about in any large dataset.
Microsoft Excel Practice Test Questions
Prepare for the Microsoft Excel exam with our free practice test modules. Each quiz covers key topics to help you pass on your first try.
Microsoft Excel Excel Basic and Advance
Microsoft Excel Exam Questions covering Excel Basic and Advance. Master Microsoft Excel Test concepts for certification prep.
Microsoft Excel Excel Formulas
Free Microsoft Excel Practice Test featuring Excel Formulas. Improve your Microsoft Excel Exam score with mock test prep.
Microsoft Excel Excel Functions
Microsoft Excel Mock Exam on Excel Functions. Microsoft Excel Study Guide questions to pass on your first try.
Microsoft Excel Excel MCQ
Microsoft Excel Test Prep for Excel MCQ. Practice Microsoft Excel Quiz questions and boost your score.
Microsoft Excel Excel
Microsoft Excel Questions and Answers on Excel. Free Microsoft Excel practice for exam readiness.
Microsoft Excel Excel Trivia
Microsoft Excel Mock Test covering Excel Trivia. Online Microsoft Excel Test practice with instant feedback.
Real-World XLOOKUP Use Cases vs vlookup excel
The classic HR scenario involves looking up employee details from an ID. With VLOOKUP you had to count columns and worry about leftward lookups failing entirely. With XLOOKUP you simply write =XLOOKUP(EmpID, IDColumn, NameColumn) and the function works regardless of where the columns sit relative to each other, including when names appear to the left of the ID column, which was impossible with traditional VLOOKUP without helper columns.
Pair this with conditional formatting and a search box, and you have a one-click directory tool. Add the if_not_found argument with text like Not on Payroll to keep results readable for non-technical managers. Many HR teams have replaced full custom-built Access apps with XLOOKUP-powered Excel dashboards because the function is fast enough and friendly enough to make spreadsheets the better tool for departmental lookups today.

XLOOKUP Pros and Cons Compared to VLOOKUP and INDEX/MATCH
- +Searches left, right, up, or down with zero workarounds
- +Defaults to exact match preventing silent approximation errors
- +Built-in if_not_found argument eliminates messy IFERROR wraps
- +Returns multiple columns at once through array spilling
- +Reverse search finds the most recent match in one formula
- +Wildcard matching with match_mode 2 supports partial searches
- +Cleaner syntax that reads naturally from left to right
- โOnly available in Microsoft 365, Excel 2021, 2024, and Web
- โFiles shared with Excel 2019 users show #NAME? errors
- โSlightly slower than INDEX/MATCH on very large 1M-row datasets
- โSix arguments can intimidate beginners coming from SUM or AVERAGE
- โSome legacy training materials still teach VLOOKUP first
- โApproximate match modes require careful match_mode awareness
XLOOKUP Best Practice Checklist for Clean Workbooks
- โAlways use absolute references for lookup_array and return_array when copying down columns
- โSet the if_not_found argument explicitly rather than wrapping in IFERROR
- โConfirm your Excel version supports XLOOKUP before sharing workbooks externally
- โConvert ranges to Excel Tables so lookup arrays auto-expand with new data
- โUse match_mode 0 unless you specifically need approximate or wildcard behavior
- โTest reverse search with search_mode -1 for most-recent-record use cases
- โSpill multiple columns by selecting an adjacent return_array range
- โName your tables and columns descriptively to make formulas self-documenting
- โAvoid hard-coding lookup values in cells; reference input cells instead
- โDocument complex nested XLOOKUPs with a comment in an adjacent cell
Three Arguments Are All You Usually Need
Despite having six arguments total, roughly 80 percent of real XLOOKUP formulas use only the first three: lookup_value, lookup_array, and return_array. Master that simple pattern first, then layer on the optional arguments one at a time as edge cases appear. This progressive approach builds confidence and prevents the formula bloat that scares newcomers away.
Advanced XLOOKUP techniques unlock workflows that were either tedious or impossible in older versions of Excel. The first advanced pattern to master is nested XLOOKUP for two-way matrix lookups. Picture a price grid with product names down column A and quantity tiers across row 1. Writing =XLOOKUP(quantity, B1:F1, XLOOKUP(product, A2:A20, B2:F20)) returns the unit price at the intersection. The inner XLOOKUP retrieves the entire row for that product, then the outer XLOOKUP picks the correct quantity tier from the resulting array.
The second advanced trick is combining XLOOKUP with FILTER to return all matches instead of just the first or last one. While XLOOKUP alone returns a single matching value, FILTER returns every row meeting your criteria. Use XLOOKUP when you know exactly one match exists, and FILTER when multiple rows could qualify. Together they cover virtually every real-world search scenario, from invoice lookups to multi-row contract retrieval inside complex audit workpapers used by finance and accounting teams.
Third, XLOOKUP integrates beautifully with dynamic arrays. Functions like SORT, UNIQUE, and SEQUENCE feed naturally into XLOOKUP. For example, =XLOOKUP(UNIQUE(A2:A100), A2:A100, B2:B100) returns one matching value per unique key, instantly creating a deduplicated lookup result that previously required PivotTables or removing duplicates manually. This is a leap forward for anyone preparing summary reports from raw transaction logs that contain repeated keys across thousands of rows of data.
Fourth, XLOOKUP supports approximate matching elegantly. The match_mode -1 returns the next smaller value, while 1 returns the next larger. This is perfect for tax bracket calculations, shipping tier pricing, or grade letter assignments. For example, =XLOOKUP(score, ThresholdColumn, GradeColumn, , -1) instantly assigns A, B, C grades based on score thresholds, replacing nested IF statements that previously stretched across hundreds of characters and were nightmares to maintain.
Fifth, freezing key rows during long lookups is essential when navigating large lookup tables visually. Many analysts pair XLOOKUP with frozen panes so they can scroll through results without losing column headers.
Sixth, XLOOKUP works inside conditional formatting rules. Apply a rule with formula =B2=XLOOKUP(A2, KeyColumn, ValueColumn) to highlight cells whose live value matches a lookup result. This is invaluable for variance reports, where any mismatch between expected and actual must be visually flagged. The dynamic nature means highlights update instantly as the lookup table changes, so audit teams always see the current state of every reconciliation cell across the entire workbook in real time.
Seventh, performance optimization matters at scale. On datasets over 100,000 rows, XLOOKUP benchmarks roughly 30 percent faster than equivalent INDEX/MATCH, though the gap closes when binary search modes are activated. Use search_mode 2 for ascending-sorted data and -2 for descending-sorted data to engage binary search, which is dramatically faster than the default linear scan when row counts climb into the hundreds of thousands or even millions of records inside complex financial models today.

XLOOKUP only works in Microsoft 365, Excel 2021, Excel 2024, and Excel for the Web. If you send an XLOOKUP workbook to anyone running Excel 2019 or earlier, all your formulas will display as #NAME? errors. Always verify your audience's Excel version, or convert formulas to INDEX/MATCH before distributing files externally to clients or partners.
Error handling and compatibility planning separate professional XLOOKUP users from beginners. The most common error you will encounter is #N/A, which appears whenever XLOOKUP cannot find the lookup_value in the lookup_array. The fix is the fourth argument, if_not_found. Pass a string like Not Found, the number zero, or even an empty string with double quotes. Always set this explicitly in production workbooks because silent #N/A errors cascade into SUM and AVERAGE formulas downstream, poisoning entire dashboards and breaking executive reports.
The second error type is #VALUE!, which occurs when the lookup_array and return_array have mismatched sizes. If lookup_array is ten rows tall but return_array is only eight rows tall, Excel cannot align them. Always check both ranges match by selecting them and reviewing the size in the lower-right status bar. Excel Tables eliminate this problem entirely because column references inside a table always span the same number of rows automatically as new data is added or removed throughout the file.
The third common error is #NAME?, which means the user's Excel version does not recognize the XLOOKUP function. This is almost always a compatibility issue rather than a formula bug. To fix it for legacy audiences, convert XLOOKUP into the equivalent INDEX/MATCH expression. For example, =XLOOKUP(A1, B:B, C:C) becomes =INDEX(C:C, MATCH(A1, B:B, 0)). This older syntax works in every version of Excel since 2010 and is the safest format for files distributed across organizations with mixed software versions.
The fourth issue is #SPILL!, raised when the return_array is wider or taller than one cell but neighboring cells are not empty. XLOOKUP needs blank space to spill multi-column or multi-row results. Either clear the cells blocking the spill range, or restructure the formula to return a single value. The @ implicit intersection operator can also force XLOOKUP to return just one value, which is useful when you only need a scalar result inside a larger compound formula calculation.
The fifth troubleshooting topic is unexpected matches caused by mixed data types. If your lookup_array contains numbers stored as text alongside true numbers, XLOOKUP will not match them as equivalent. Use VALUE or NUMBERVALUE to standardize, or use the Convert to Number option from the green triangle indicator. This single issue causes more lookup failures in real businesses than any other, especially when data is imported from external systems like SAP, QuickBooks, or various CRM exports stored in CSV format files.
For long-term compatibility, document your XLOOKUP usage in a notes worksheet so future colleagues understand the dependency on Microsoft 365 features. Add version requirements to file names like CompanyDashboard_M365.xlsx so recipients immediately know what they need. Some IT departments still standardize on Excel 2019 for security reasons, so confirming environments before adoption prevents painful rollbacks. Training peers on XLOOKUP also accelerates organizational uptake and reduces ongoing support tickets that always seem to appear after any new Excel feature first arrives.
Finally, build a personal library of reusable XLOOKUP snippets. Save common patterns like reverse-search-for-latest, wildcard-search-with-fallback, and two-way-matrix-lookup in a OneNote or a Notes tab inside a master workbook. Over time this library becomes your secret productivity weapon, letting you assemble complex workbooks in minutes rather than hours. Combine these snippets with clean named ranges and structured Excel Tables to create truly maintainable spreadsheets that future analysts will actually understand and extend confidently for years to come.
You can continue preparing with the microsoft xl, which includes answer explanations for every question.
You can continue preparing with the xcel online study course, which includes answer explanations for every question.
You can continue preparing with the excel test practice, which includes answer explanations for every question.
Practical adoption of XLOOKUP works best when you commit to a one-week practice plan. On day one, replace every new VLOOKUP you write with XLOOKUP. Do not migrate old formulas yet, simply build new habits going forward. On day two, practice the if_not_found argument until it becomes muscle memory. On day three, experiment with reverse search and wildcard matching on a sample dataset. By day four you will already feel more productive than you ever did with VLOOKUP across nearly every common lookup scenario you face.
For days five through seven, tackle progressively harder use cases. Build a two-way lookup matrix, integrate XLOOKUP with FILTER for multi-row returns, and try spilling multiple columns at once. Each day, save one example into a personal reference workbook with clear labels. By the end of week one you will have a library of seven or eight working patterns that cover roughly 95 percent of the lookups you will ever write in a typical business context for years to come.
In week two, start migrating older VLOOKUP formulas opportunistically. Whenever you open an older workbook to update something, replace any VLOOKUPs in cells you are already touching. This avoids the trap of trying to migrate everything at once, which often introduces new bugs. The opportunistic approach gradually modernizes your portfolio of files without overwhelming your schedule and without breaking working formulas that have been quietly running correctly for months or even years inside production reports.
Pair XLOOKUP learning with companion skills to multiply its value. Learning Excel Tables turns every XLOOKUP into a self-expanding formula. Learning conditional formatting layers visual signals on top of XLOOKUP results. Learning Power Query feeds clean, deduplicated data into your lookup tables. Together these four skills form a foundation that most professional Excel users in 2026 expect mid-level analysts to possess on day one of any new role across finance, operations, marketing, or general business analytics functions.
Do not skip the practice quizzes attached to this article. Active recall through multiple choice and scenario-based questions builds memory faster than passive reading. Test yourself on argument order, match_mode behavior, and search_mode reverse logic until you can answer in seconds rather than minutes. Free quizzes also cover related topics like data validation, drop-down lists, deduplication, and merging cells, which all pair beautifully with XLOOKUP in real production workbooks for everyday business reporting tasks today.
If you teach others Excel, lead with XLOOKUP, not VLOOKUP. New learners benefit from starting with the modern function because they avoid building habits that they will later need to unlearn. Explain VLOOKUP only as historical context. This approach mirrors how programming instructors stopped teaching Python 2 once Python 3 matured. Excel has reached the same inflection point with lookups in 2025, and 2026 will be the year XLOOKUP becomes the only lookup function taught in serious training programs.
Finally, remember that XLOOKUP is just one tool in a broader analytical toolkit. The best analysts blend XLOOKUP with charts, PivotTables, conditional formatting, and statistical functions to create insights, not just data lookups. Treat XLOOKUP as a foundational skill that supports higher-order analysis rather than an end in itself. With consistent daily use over a few weeks, this single function will quietly become the most-used formula in your entire Excel vocabulary, freeing time for the harder analytical thinking your role actually rewards.
Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com โ Retake policy, test delivery, time limit, eligibility ยท verified Oct 2026
- certiport.pearsonvue.com โ Exam format, score validity, passing score, score release, exam information ยท verified Oct 2026




