XLOOKUP is the Excel function that quietly fixed everything people used to get wrong with VLOOKUP. It does the same job, finding a value in one place and returning the matching value from another, but without the column counting, the silent wrong answers, or the formulas that break the moment somebody inserts a column.
Most people who still reach for VLOOKUP do so out of habit rather than preference. It has been the standard for two decades, it is what everyone was taught, and it works well enough that the effort of learning something new rarely feels urgent. The catch is that VLOOKUP’s default behaviour returns approximate matches, which produces answers that look perfectly plausible and are quietly wrong.
What follows is the syntax, a set of worked examples you can copy straight into a spreadsheet, the trick for matching on two criteria at once, and an honest note on which versions of Excel actually have it. If you have been meaning to make the switch, this should take about ten minutes.
Quick summary
The case for switching is stronger than most people realise, because XLOOKUP does not just add features, it removes the specific behaviours that make VLOOKUP error-prone:
The catch is version availability, not capability: XLOOKUP does not exist in Excel 2016 or 2019, which is the reason some organisations are still writing VLOOKUP in 2026.
What is XLOOKUP?
XLOOKUP is a lookup function in Excel that searches a range or array and returns the matching item from a second range. If no match is found, it can return a value you specify or fall back to the closest approximate match. Microsoft introduced it to fix the accumulated limitations of VLOOKUP, HLOOKUP and the INDEX MATCH combination, replacing all three with one function.
The practical difference is that XLOOKUP asks you to point at two ranges, the one to search and the one to return from, rather than asking you to count columns. That single change removes most of the fragility people associate with lookups. Microsoft’s own XLOOKUP function reference documents every argument in full.
XLOOKUP syntax
The full syntax has six arguments, but only the first three are required, and in everyday use those three are usually all you need:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value is what you are searching for.
- lookup_array is the range you are searching in.
- return_array is the range you want the answer from.
- if_not_found is what to display when nothing matches, which is optional but well worth using.
- match_mode controls how strictly it matches, and defaults to exact.
- search_mode controls the direction of the search, and defaults to first to last.
The important detail hiding in that list is the default on match_mode. XLOOKUP defaults to an exact match, whereas VLOOKUP defaults to approximate. That one difference prevents a whole category of quiet errors.
Worked examples
These are written so you can copy them into a sheet and adapt the ranges. Assume a table where column B holds product codes, column C holds product names and column D holds prices.
A basic lookup
To find the price for the product code in cell G2:
=XLOOKUP(G2, B2:B100, D2:D100)Three arguments, no column counting, and an exact match by default. This is the formula that will cover most of your day-to-day work.
Handling a missing value
If the code is not found, the formula above returns #N/A. The fourth argument replaces that with something more useful:
=XLOOKUP(G2, B2:B100, D2:D100, "Code not found")With VLOOKUP this required wrapping the whole formula in IFERROR, which also masked genuine errors. Here it is built in and only catches a missing match.
Looking to the left
Suppose the price is in column B and the product code is in column D. VLOOKUP cannot do this at all without rearranging your data. XLOOKUP does not care about column order:
=XLOOKUP(G2, D2:D100, B2:B100)Returning several columns at once
Because XLOOKUP can return an array, one formula can bring back multiple fields. To return the name and the price together:
=XLOOKUP(G2, B2:B100, C2:D100)The result spills into the adjacent cells automatically, so a single formula fills two columns.
Matching on two criteria
This is the question people search for most often after the basics, and the answer is neater than the equivalent VLOOKUP workaround. Multiply the two conditions together and look up the value 1:
=XLOOKUP(1, (B2:B100=G2)*(C2:C100=H2), D2:D100)Each comparison produces TRUE or FALSE, multiplying them gives 1 only where both are true, and XLOOKUP finds that row. No helper column and no concatenation required.
Finding the most recent entry
By default XLOOKUP searches from the top down. Setting the sixth argument to -1 searches from the bottom up, which is how you find the latest record in a log:
=XLOOKUP(G2, B2:B100, D2:D100, "Not found", 0, -1)Worth knowing about XLOOKUP
Two error messages account for most XLOOKUP problems, and both are easy to diagnose. A #NAME? error almost always means your version of Excel does not have the function, rather than a typo in the formula. A #VALUE! error usually means your lookup array and return array are different sizes, for example searching B2:B100 but returning from D2:D50. Check the row counts match before looking anywhere else.
Why XLOOKUP beats VLOOKUP
Having taught both to a great many people, the argument that lands is not the feature list, it is the failure modes. VLOOKUP’s defaults work against you in ways that are easy to miss. The differences that matter in practice are these:
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Default match type | Approximate, unless you remember the fourth argument | Exact |
| How it finds the answer | Counts columns from the left using an index number | Points directly at a return range |
| Effect of inserting a column | Formula silently returns the wrong field | Formula continues to work |
| Search direction | Right of the lookup column only | Left or right |
| Missing value handling | Requires wrapping in IFERROR | Built into the fourth argument |
| Returning several fields | One formula per column | One formula can return multiple columns |
| Searching from the bottom up | Not possible | Set search_mode to -1 |
| Excel versions supported | All versions | Microsoft 365, web, 2021, 2024 |
Reading down that table, the pattern is clear enough: XLOOKUP does not simply add capability, it removes the specific behaviours that cause VLOOKUP formulas to fail quietly. The four that cause the most trouble are worth spelling out:
- Approximate matching by default. VLOOKUP’s fourth argument is optional, and omitting it gives you an approximate match. The formula returns a number, nobody questions it, and it is wrong. XLOOKUP defaults to exact.
- Column index numbers break. VLOOKUP counts columns from the left, so inserting a column shifts everything and the formula silently returns the wrong field. XLOOKUP points at the actual return range instead.
- No leftward lookups. VLOOKUP can only return values to the right of the lookup column, which forces people to rearrange data or fall back on INDEX MATCH.
- Clumsy error handling. Catching a missing value meant wrapping the formula in IFERROR, which hid real problems too.
None of this makes VLOOKUP useless, and there is no need to rewrite working spreadsheets for the sake of it. It does mean that for anything new, XLOOKUP is the better default.
Which versions of Excel have XLOOKUP?
This is the practical constraint, and it is worth checking before you commit to rewriting anything. XLOOKUP is available in Excel for Microsoft 365, Excel for the web, Excel 2021 and Excel 2024, across Windows and Mac, plus the mobile apps.
It is not available in Excel 2016 or Excel 2019. If you open a workbook containing XLOOKUP in one of those versions, the formula will show a #NAME? error even though it works perfectly for the person who wrote it. That matters when sharing files with clients or suppliers on older perpetual licences, and it is the one genuine reason to stick with VLOOKUP or INDEX MATCH for a shared workbook.
Common mistakes worth avoiding
A handful of issues account for nearly every XLOOKUP question that comes up in a classroom.
- Mismatched array sizes. The lookup array and return array must contain the same number of rows. This causes the #VALUE! error described above.
- Assuming it exists everywhere. Writing XLOOKUP into a workbook that colleagues open in Excel 2019 will break it for them, not for you.
- Using entire column references. Referencing B:B and D:D works, but on large workbooks it slows recalculation noticeably. Reference the actual range.
- Forgetting match_mode when you want approximate. For commission bands or tax brackets you need approximate matching, so set the fifth argument to -1 for exact or next smaller.
- Rewriting everything at once. Existing VLOOKUP formulas that work are not urgent. Use XLOOKUP for new work and convert the rest as you touch it.
Get past those and XLOOKUP becomes the function you reach for without thinking, which is exactly where you want it.
When the answer is not a formula at all
One honest caveat, because it comes up constantly. If a spreadsheet has grown into a shared tracker that several people update, with a status column and an owner column, the problem is no longer a lookup problem. At that point the right move is usually to stop patching formulas and move the data somewhere built for it, which our post on Microsoft Lists covers in detail.
XLOOKUP is excellent at what it does. It is not a fix for a spreadsheet doing a job a spreadsheet should not be doing.
Is your team’s Excel knowledge stuck in 2010?
Most people are self-taught, which means they use the functions they happened to learn first rather than the ones that would save them hours. Our hands-on Excel training covers modern functions like XLOOKUP properly, at foundation, intermediate and advanced level. Tell us what your team actually does in Excel and we will tell you honestly which level they need, or whether they need training at all.
The bottom line on XLOOKUP
XLOOKUP is the rare upgrade that is genuinely simpler than the thing it replaces. It removes the column counting, defaults to the behaviour you actually want, handles missing values without a wrapper, and looks in both directions. If you learned Excel before 2020, switching costs you about ten minutes and removes a category of error you may not have realised you were exposed to.
The only real constraint is version availability, so check what your colleagues and clients are running before rewriting a shared workbook. Beyond that, use XLOOKUP for anything new and convert older formulas as you happen to touch them.
If your team would benefit from working through this properly rather than picking it up piecemeal, our Excel training courses run at foundation, intermediate and advanced level, and our post on essential Excel skills covers what else is worth knowing. Making new skills stick after training matters just as much, something Vantage 365 Support has written about separately.
STRAIGHT ANSWERS
Frequently asked questions
What is XLOOKUP used for?
XLOOKUP searches a range for a value and returns the corresponding item from a second range. It is used for any lookup task: matching a product code to a price, an employee number to a department, or a postcode to a region. It replaces VLOOKUP, HLOOKUP and the INDEX MATCH combination with a single function.
What is the difference between XLOOKUP and VLOOKUP?
The main differences are that XLOOKUP returns an exact match by default while VLOOKUP returns an approximate match by default, XLOOKUP points at a return range instead of counting column numbers, XLOOKUP can look to the left of the lookup column, and XLOOKUP has built-in handling for values that are not found. In practice this means fewer silent errors and fewer formulas breaking when columns move.
Is XLOOKUP better than INDEX MATCH?
For most purposes yes, because it does the same job in one function rather than two nested ones, and it is far easier to read months later. INDEX MATCH still has a place when you need to look up a row and a column at the same time, or when you are working in a version of Excel without XLOOKUP.
Can XLOOKUP handle multiple criteria?
Yes, without a helper column. Multiply the conditions together and look up the value 1, for example =XLOOKUP(1, (B2:B100=G2)*(C2:C100=H2), D2:D100). Each comparison returns TRUE or FALSE, and multiplying them gives 1 only where every condition is met.
Why is XLOOKUP not working in my version of Excel?
If you see a #NAME? error, your version almost certainly does not support it. XLOOKUP is available in Microsoft 365, Excel for the web, Excel 2021 and Excel 2024, but not in Excel 2016 or Excel 2019. If the formula works for you but not a colleague, they are probably on an older perpetual licence.
Can XLOOKUP return more than one value?
Yes. Because it can return an array, specifying a return range that spans several columns will bring back all of them, and the result spills into the adjacent cells automatically. One formula can therefore return a name, a department and a start date together.
