XLOOKUP in Excel is a lookup function available in Excel 2021 and Microsoft 365 that searches a range for a value and returns a result from a separate range. It replaces VLOOKUP and HLOOKUP, works in any direction, handles missing values natively with a built-in argument, and can return multiple columns in a single formula.
Key Takeaways
- XLOOKUP in Excel is available in Microsoft 365 (all plans) and Excel 2021 onward. It is not available in Excel 2019 or earlier.
- It can look left, right, up or down. VLOOKUP can only look to the right of the lookup column.
- The if_not_found argument lets you show a custom message instead of a #N/A error, with no extra IFERROR wrapper needed.
- XLOOKUP matches exact values by default. VLOOKUP defaults to approximate match, which causes silent errors if your data is not sorted.
- Excel skills consistently appear in LinkedIn’s Jobs on the Rise India 2023 report as one of the most in-demand analytical skills for roles across India’s finance, consulting and tech sectors.
What Is XLOOKUP in Excel?
XLOOKUP in Excel is a modern lookup function introduced by Microsoft in August 2019 as part of a wider push to modernise the formula engine in Microsoft 365. It was designed specifically to fix the well-documented limitations of VLOOKUP, HLOOKUP and even the INDEX MATCH workaround that analysts had been using for years. If you work in Excel 365 or Excel 2021, XLOOKUP in Excel is the lookup function you should be using by default for all new work.
Which Excel Versions Support XLOOKUP
Availability is the first practical question, because nothing is more frustrating than building a formula that breaks the moment you share a file. Here is what you need to know:
| Excel Version | XLOOKUP Available? | Notes |
|---|---|---|
| Microsoft 365 (all plans) | Yes | Fully supported, updated regularly |
| Excel 2021 (perpetual licence) | Yes | Included at launch |
| Excel 2019 | No | Use INDEX MATCH instead |
| Excel 2016 / 2013 | No | VLOOKUP or INDEX MATCH only |
| Excel for the Web (Office Online) | Yes | Free with a Microsoft account |
| Excel for Mac (365 subscription) | Yes | Parity with Windows version |
If your organisation still runs Excel 2019, check whether a Microsoft 365 subscription is available. Many Indian universities and companies have enterprise agreements that include it at no additional cost per user.
According to Microsoft’s usage telemetry shared at Ignite 2023, XLOOKUP in Excel has become one of the top five most-used functions in Microsoft 365 Excel, overtaking HLOOKUP entirely. That is a meaningful signal about where the industry has moved.
According to Statista’s 2023 global productivity software survey, Microsoft Excel is used by over 750 million people worldwide, making formula literacy one of the highest-leverage skills any analyst can develop.
XLOOKUP Syntax and Arguments
The full XLOOKUP formula syntax is: =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 genuinely useful. Here is what each one does:
| Argument | Required? | What It Does | Example Value |
|---|---|---|---|
| lookup_value | Yes | The value you are searching for | “Ravi Kumar” or A2 |
| lookup_array | Yes | The column or row to search in | B2:B100 |
| return_array | Yes | The column or row to return a value from | D2:D100 |
| if_not_found | No | Text to show when no match is found | “Not Found” |
| match_mode | No | 0 = exact, -1 = next smaller, 1 = next larger, 2 = wildcard | 0 |
| search_mode | No | 1 = first to last, -1 = last to first, 2 = binary ascending | 1 |
The default match_mode is 0 (exact match). This alone fixes the most common VLOOKUP mistake, where users forget to type FALSE as the fourth argument and get wrong results on unsorted data. XLOOKUP in Excel is safe by default.
A Practical Note on if_not_found
In VLOOKUP, you would wrap the entire formula in IFERROR to handle missing values: =IFERROR(VLOOKUP(…), “Not Found”). With the XLOOKUP formula, you simply add “Not Found” as the fourth argument. Fewer nested functions means fewer mistakes and faster auditing when a colleague needs to understand your workbook.
XLOOKUP Formula Examples
Here are three real scenarios, each one a direct upgrade of a VLOOKUP formula you have probably written before.
Example 1: Basic Employee Lookup
Say you have employee IDs in column A and salaries in column E. With VLOOKUP you had to count columns: =VLOOKUP(H2, A2:E100, 5, FALSE). If someone inserts a column, that “5” breaks silently.
With the XLOOKUP formula: =XLOOKUP(H2, A2:A100, E2:E100, “ID not found”). You reference the exact return column directly. Insert or delete a column anywhere and the formula adjusts automatically because it tracks the range, not a column number. MIS analysts at BFSI firms in Mumbai commonly use this pattern to pull salary bands from HR master sheets without restructuring data.
Example 2: XLOOKUP Looking Left
Suppose your product codes are in column D and the product names are in column B, to the left. VLOOKUP cannot do this without restructuring your data. INDEX MATCH can, but the syntax trips up beginners.
XLOOKUP in Excel handles it directly: =XLOOKUP(G2, D2:D200, B2:B200, “Product not found”). The lookup_array is D and the return_array is B. Direction does not matter. This is the single most common reason experienced analysts switch from VLOOKUP to XLOOKUP immediately.
Example 3: XLOOKUP Multiple Columns at Once
Imagine you are building a sales dashboard for a retail chain with stores across Mumbai, Delhi and Bengaluru. You want to pull the store name, city and monthly target for a given store ID in one formula, not three separate lookups.
Set your return_array to span multiple columns: =XLOOKUP(J2, A2:A50, C2:E50, “Store not found”). Excel spills the results across three adjacent cells automatically. This spill behaviour, part of Excel’s dynamic array engine, makes XLOOKUP far more efficient for dashboard building than anything VLOOKUP could offer.
Common Mistake to Avoid
Do not mix up lookup_array and return_array sizes. If lookup_array has 100 rows and return_array has 99, Excel throws a #VALUE error. Always make sure both ranges span the same number of cells. This catches out even experienced analysts when they are working quickly.
XLOOKUP vs VLOOKUP vs INDEX MATCH
This comparison comes up in almost every data analyst interview, so it is worth knowing the real differences rather than a vague “XLOOKUP is newer and better.” Here is the honest breakdown:
| Feature | VLOOKUP | INDEX MATCH | XLOOKUP |
|---|---|---|---|
| Look left | No | Yes | Yes |
| Look up (horizontal) | No (use HLOOKUP) | Yes | Yes |
| Default match type | Approximate (dangerous) | Exact (safe) | Exact (safe) |
| Built-in error handling | No (needs IFERROR) | No (needs IFERROR) | Yes (if_not_found) |
| Return multiple columns | No | Possible but complex | Yes (dynamic spill) |
| Column insertion safety | No (breaks on insert) | Yes | Yes |
| Wildcard matching | Yes | With workaround | Yes (match_mode 2) |
| Excel version required | All versions | All versions | Excel 2021 or 365 |
INDEX MATCH is still worth knowing. If you are ever working in Excel 2019 or sharing files with colleagues on older versions, INDEX MATCH is your best option. But when everyone on your team has Microsoft 365, there is no good reason to use VLOOKUP over XLOOKUP for new work.
According to NASSCOM’s India Tech Report 2023, data-related roles including data analysts, BI developers and MIS executives are among the fastest-growing job categories in India’s IT and services sector, with over 11 lakh new openings projected through 2025. Excel proficiency, including advanced functions like XLOOKUP in Excel, consistently appears in the required skills section of these postings on platforms like Naukri.com and LinkedIn India.
If you are preparing to make a career move into analytics, check out our guide on data analyst vs data scientist to understand which path fits your background, and browse our list of data analytics projects you can build to strengthen your portfolio.
Excel is often the starting point, but analysts working at scale eventually move to Python tools. Our breakdown of Pandas and NumPy shows how the same lookup and filter logic you practise in Excel maps directly to Python data manipulation. If you are thinking about which skills pay best, our highest paying tech skills guide has current salary data for India and global markets.
For anyone starting from scratch, our Hindi-language guide on data analyst kaise bane walks through the full learning roadmap in plain language.
Frequently Asked Questions
What is XLOOKUP in Excel?
XLOOKUP in Excel is a lookup function that searches a specified range for a value and returns a corresponding result from a separate range. It works in any direction, handles missing values with a built-in if_not_found argument, and returns multiple columns in a single formula. It is available in Excel 2021 and all Microsoft 365 plans.
Is XLOOKUP better than VLOOKUP?
For most practical use cases, yes. XLOOKUP in Excel defaults to exact matching (VLOOKUP defaults to approximate, which causes silent errors), it can look in any direction, it does not break when you insert columns, and it has a built-in way to handle missing values. The only reason to use VLOOKUP in new work is if your file must be compatible with Excel 2019 or older.
Which Excel versions have XLOOKUP?
XLOOKUP is available in Microsoft 365 (all subscription plans, including personal, family and business), Excel 2021 (perpetual licence), and Excel for the Web. It is not available in Excel 2019, 2016 or 2013. If you are unsure which version you have, go to File, then Account, and check under Product Information.
Can XLOOKUP look to the left?
Yes, and this is one of the main reasons analysts switch to it. Unlike VLOOKUP, which requires the lookup column to be the leftmost column in your range, XLOOKUP in Excel lets you specify the lookup_array and return_array independently. Your return column can be anywhere, to the left, right, above or below the lookup column.
How do I return a default value with XLOOKUP?
Use the fourth argument, if_not_found. For example: =XLOOKUP(A2, D2:D100, E2:E100, “Not found”). When the lookup value is not in the lookup_array, Excel shows “Not found” instead of a #N/A error. You do not need to wrap the formula in IFERROR, which keeps your XLOOKUP formula shorter and easier to audit.
What is the difference between XLOOKUP and INDEX MATCH?
Both can look in any direction and both default to exact matching. XLOOKUP in Excel is simpler to write because it uses a single function rather than two nested functions. INDEX MATCH works in all Excel versions including 2019 and older, making it the better choice when file compatibility matters. For Microsoft 365 users, XLOOKUP is the cleaner option.
XLOOKUP in Excel is the clearest upgrade available in the modern Excel formula toolkit, and learning it properly takes less than an hour. Here are three things you can do this week to make it stick:
- Open an existing workbook that uses VLOOKUP and rewrite three of those formulas as XLOOKUPs, paying attention to the if_not_found argument each time.
- Practise a left-lookup on your own data: find a case where your return column sits to the left of your lookup column and write the XLOOKUP formula from scratch.
- Build a small spill-based lookup that returns two or three columns at once, then use it as the data source for a simple chart or summary table.
Excel and data skills do not stop at lookup functions. Analysts who can filter logs, build dashboards and spot patterns in large datasets are in demand across cybersecurity teams and Security Operations Centres as much as they are in finance or consulting. If you want to take those skills into a high-demand security career, explore the EC-Council Certified SOC Analyst course at 3.0 University, where data-driven thinking meets real-world threat detection. You can also browse the full range of 3.0 University online certification courses in Cybersecurity, AI, Ethical Hacking and Blockchain to find the next step that fits your goals.
Last updated: May 2025. Reviewed by the 3University editorial team.