XLOOKUP is the better function, so use it whenever everyone who opens your workbook has Microsoft 365, Excel 2021 or a later version. On features, XLOOKUP vs VLOOKUP is barely a contest: XLOOKUP matches exactly by default, looks left as easily as right, and doesn't break when somebody inserts a column. VLOOKUP still matters when a file has to work in Excel 2019 or earlier, and you'll be meeting it in older workbooks for years.
Both functions do the same basic job. You give them something to find, such as a product code, and they bring back a related value from the same row, such as its price. The difference is how much they trust you to get the details right. VLOOKUP trusts you completely, and that is the problem.
This guide shows how each one works, with formulas you can copy, the four classic VLOOKUP traps, where INDEX and MATCH fit, and what to do if your Excel doesn't have XLOOKUP at all. Every syntax detail matches Microsoft's own help pages, linked as we go.
What is XLOOKUP and how does it work?
XLOOKUP searches one range for a value and returns whatever sits in the matching position of another range. Microsoft's XLOOKUP help page sets out the syntax like this:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Only the first three arguments are required. Lookup_value is what you're looking for, lookup_array is the column to search, and return_array is the column you want an answer from. The three optional ones let you set a message for when nothing is found, change how it matches, and change which end it searches from.
The simplest version, finding the price for a product code in cell A2, reads almost like a sentence: =XLOOKUP(A2, Products[Code], Products[Price]). Find A2 in the Code column and return the Price from the same row. Products is an Excel table here, which is worth setting up (select your data and press Ctrl+T on Windows) because column names make formulas readable and the ranges grow as you add rows.
Two defaults do most of the good work. XLOOKUP looks for an exact match unless you tell it otherwise, and when nothing matches it returns #N/A rather than a nearby value that looks plausible.
How VLOOKUP works, and its four classic problems
VLOOKUP is the lookup most people learned first, so it's in countless templates and inherited reports. Its syntax, from Microsoft's VLOOKUP help page, is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
You give it a value, a block of cells, the number of the column you want back (counting from 1 at the left of the block) and, optionally, TRUE or FALSE for an approximate or exact match. A typical working formula looks like =VLOOKUP(A2, $D$2:$G$200, 3, FALSE). It works. It also has four habits that catch people out:
It only looks right. The value you search for must sit in the first column of the block, and the answer has to come from a column to its right. If your product names sit to the left of the codes, VLOOKUP can't fetch them unless you rearrange the sheet.
The column number breaks quietly. That 3 means "the third column of the block". Insert a new column inside the block, before the one you wanted, and the 3 now points at something else, so the formula returns the wrong data with no error to warn you.
It assumes approximate match. Leave off the last argument and VLOOKUP treats it as TRUE, which assumes the first column is sorted and returns the closest value it finds. On unsorted data that can be a perfectly believable wrong answer. Typing FALSE every time is the fix, and forgetting it once is the trap.
Missing values need a wrapper. When an exact match fails you get #N/A, and the usual cure is wrapping the whole formula: =IFERROR(VLOOKUP(A2, $D$2:$G$200, 3, FALSE), "Not found"). That works, but IFERROR hides every error, including the ones that mean your formula is broken. IFNA, which only catches #N/A, is the safer wrapper.
Mentioned in this article
Certificate in Microsoft Excel
Self-paced · verifiable certificate · £89
View courseHow to use XLOOKUP in Excel: six worked examples
These examples use two tables. Products has the columns Name, Code, Category and Price, in that order. Orders is a log of sales with a Code, a Date and the Price charged, newest at the bottom. The product code you're looking up is in A2.
If your data isn't in a table, ordinary ranges work exactly the same way, as long as the lookup range and the return range line up row for row: =XLOOKUP(A2, $B$2:$B$500, $D$2:$D$500).
Find a price: =XLOOKUP(A2, Products[Code], Products[Price]). The everyday lookup, an exact match with no FALSE to remember.
Show a friendly message: =XLOOKUP(A2, Products[Code], Products[Price], "Not found"). The fourth argument replaces #N/A, so there's no IFERROR wrapper hiding other mistakes.
Look to the left: =XLOOKUP(A2, Products[Code], Products[Name]). Name sits to the left of Code, which VLOOKUP can't reach. XLOOKUP doesn't mind which side the answer is on.
Return several columns at once: =XLOOKUP(A2, Products[Code], Products[[Category]:[Price]]). One formula brings back both Category and Price, and the results spill into the cells to the right. Keep those cells empty, or you'll get a #SPILL! error instead.
Find the most recent match: =XLOOKUP(A2, Orders[Code], Orders[Price], "No orders yet", 0, -1). A search_mode of -1 searches from the bottom up, so in a log kept in date order you get the latest price charged rather than the first.
Match a band, such as a commission rate for the sales figure in B2: =XLOOKUP(B2, Rates[From], Rates[Rate], 0, -1). A match_mode of -1 means an exact match, or the next smaller value if there isn't one, which is the job VLOOKUP's approximate match used to do. Here the fourth argument returns 0 for anything below the lowest band.
XLOOKUP vs VLOOKUP: the differences side by side
Put the two next to each other and the pattern is clear. VLOOKUP asks you to count, XLOOKUP asks you to point, and pointing is much harder to get wrong.
Direction: VLOOKUP only returns values from columns to the right of the lookup column. XLOOKUP returns from any column, left or right.
Default match: VLOOKUP is approximate unless you type FALSE. XLOOKUP is exact unless you ask for something else.
Choosing the answer column: VLOOKUP uses a number you count. XLOOKUP uses a range you select, so inserting or moving columns doesn't change the answer.
Nothing found: VLOOKUP returns #N/A and needs IFERROR or IFNA around it. XLOOKUP has an if_not_found argument built in.
Results per formula: VLOOKUP returns one value. XLOOKUP can return several columns at once.
Search order: VLOOKUP reads from the top. XLOOKUP can also search from the bottom up to find the last match.
Availability: VLOOKUP works in every version of Excel. XLOOKUP needs Microsoft 365, Excel 2021 or Excel 2024 on Windows or Mac, or the Excel mobile apps, and it isn't in Excel 2019 or 2016.
Where INDEX and MATCH fit in
Before XLOOKUP arrived, the standard fix for VLOOKUP's limits was to combine two functions, INDEX and MATCH. Microsoft's guide to looking up values with VLOOKUP, INDEX or MATCH still recommends the pair when your sheet isn't laid out left to right. The same look-to-the-left lookup as above becomes:
=INDEX(Products[Name], MATCH(A2, Products[Code], 0))
MATCH finds which row the code is on, and INDEX returns the value from that row of the Name column. The 0 asks MATCH for an exact match, and it's the part people forget. Like XLOOKUP, the pair can look left and doesn't care when columns are inserted. Unlike XLOOKUP, it works in every version of Excel.
So INDEX and MATCH is the in-between. If you have XLOOKUP, use it, because it's easier to read and easier to check. If you're building something that has to open in Excel 2019 or older, INDEX and MATCH gives you most of XLOOKUP's strengths without leaving anyone behind. You'll also find it all over workbooks built before XLOOKUP existed, so it's worth being able to read.
Why doesn't my Excel have XLOOKUP?
If you type =XLOOKUP and Excel doesn't recognise it, your version is too old. Microsoft's help page states plainly that XLOOKUP isn't available in Excel 2016 or Excel 2019. It is in Microsoft 365, Excel 2021 and Excel 2024 on Windows and Mac, and in the Excel apps for iPad, iPhone and Android.
To check what you have on Windows, go to File, then Account, and look under Product Information. A one-off licence bought a few years ago is the usual reason, and so is a work computer running an older version that IT manages.
You have two good options: upgrade, or use INDEX and MATCH, which does the same job in every version. The one thing not to do is build a workbook full of XLOOKUP for colleagues on older versions. The formulas won't work on their machines, and they'll find out at the worst possible moment.
Does Google Sheets have XLOOKUP?
Yes. Google Sheets has its own XLOOKUP function with the same six arguments in the same order, under slightly different names: search_key, lookup_range, result_range, missing_value, match_mode and search_mode. The defaults match too (an exact match, searching from the first entry to the last), so a formula like =XLOOKUP(A2, B2:B500, D2:D500, "Not found") behaves the same way in both.
That makes XLOOKUP a reasonable choice for files that move between Excel and Sheets. Test the finished file in both before you rely on it, because a workbook is more than its lookups, and not every feature crosses over.
Do you need a course to learn XLOOKUP?
No, and we'd rather tell you that than sell you one. If XLOOKUP is the one thing you came for, Microsoft's help page and ten minutes of practice on your own data is enough. Build the six examples above in a scratch workbook, break them on purpose, and you'll have it.
A course earns its keep when lookups are part of a bigger job: pulling together a monthly report, cleaning exports from three different systems, building a model somebody else has to trust. That is where knowing which tool fits, and why, saves real hours every week.
Our Certificate in Microsoft Excel takes you from your first formula through VLOOKUP and INDEX MATCH to XLOOKUP and FILTER, with written notes and practice questions as you go. If you already know the basics, the Advanced Certificate in Microsoft Excel has a whole module that sets VLOOKUP, HLOOKUP, XLOOKUP, INDEX MATCH, OFFSET and FILTER side by side, so you learn which one each job calls for. And if your lookups feed analysis, Excel for Data Analysis moves on to Power Query, Power Pivot and dynamic arrays.
Want all three? The Microsoft Excel Bundle includes them for less than buying each one separately. And once your reports outgrow a spreadsheet, our guide to building a Power BI dashboard is the natural next read. Every course is a one-off payment with lifetime access and a certificate employers can verify online, and the free info pack shows exactly what each one covers before you spend anything.
Good to know
Common questions
Yes, for almost every job. XLOOKUP matches exactly by default, returns values from either side of the lookup column, survives inserted columns and has a built-in message for missing values. The one thing VLOOKUP does better is work in Excel 2019 and earlier.
XLOOKUP is an Excel function that finds a value in one range and returns the matching value from another, such as finding a product code and returning its price. It's Microsoft's improved replacement for VLOOKUP, and it's available in Microsoft 365, Excel 2021 and Excel 2024.
Your version of Excel is older than XLOOKUP. Microsoft says it isn't available in Excel 2016 or Excel 2019, so you need Microsoft 365, Excel 2021 or Excel 2024. Until then, INDEX and MATCH does the same job in every version.
Yes, enough to read and fix it. Older workbooks and templates are full of VLOOKUP, and files that must open in older versions of Excel still need it or INDEX MATCH. For anything new, write XLOOKUP as long as everyone using the file has a version that supports it.
For readability, yes. XLOOKUP does in one function what INDEX and MATCH do in two, and it adds a not-found message and a bottom-up search. INDEX MATCH still wins on compatibility, because it works in every version of Excel.
Yes. Point the return_array at several adjacent columns and one formula returns them all, spilling the results into the cells to the right. Keep those cells empty, or you'll see a #SPILL! error.
Yes. Google Sheets has an XLOOKUP function with the same six arguments in the same order and the same defaults: an exact match, searching from the first entry to the last. Only the argument names are different.
