VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup should you use?

What's the difference between VLOOKUP and XLOOKUP?

XLOOKUP takes two separate ranges, matches exactly by default and looks in any direction. VLOOKUP takes one table and a column number, only searches the first column of that table, and uses an approximate match unless you add FALSE. When Microsoft announced XLOOKUP in August 2019, it called it the "successor to the iconic VLOOKUP function" (Microsoft).

That doesn't make VLOOKUP obsolete. In the same post, Microsoft called VLOOKUP its third most used function, after SUM and AVERAGE (Microsoft). Plenty of existing sheets rely on it, so you'll keep running into it for years. The real question isn't which function wins, but which one fits your sheet and the Excel version of whoever opens it.

Take the customer list of Flying Velo, a bike courier in Manchester and the fifth client in our Excel course. Every ride has a customer code in column B. On the right of the sheet is the list where dispatcher Kyle looks up who that customer is and where they're based:

Ride R01 is for customer K03, and that code is in B2. Kyle wants to know which area the ride goes to. That's in column M, to the right of the code, so all three functions can handle it. Here's the area for that customer, looked up three ways:

=VLOOKUP(B2,L2:M6,2,FALSE)
=XLOOKUP(B2,L2:L6,M2:M6)
=INDEX(M2:M6,MATCH(B2,L2:L6,0))

All three return Manchester. The differences only show when things get messier: a code that isn't on the list, a column that gets added, a name that sits to the left of the code. That's what the next sections are about, each on this same sheet.

Why does VLOOKUP return the wrong value without an error?

Because VLOOKUP uses an approximate match when you leave out the fourth argument. Microsoft's own page says the default is always TRUE if you don't specify anything (Microsoft). Approximate means: take the largest value that isn't greater than the one you're looking for. VLOOKUP assumes the first column is sorted from small to large, and it doesn't check.

You can see what that does on the Flying Velo sheet. Ride R10 is for K06, a new customer who isn't on the list yet. =VLOOKUP(B11,L2:M6,2) doesn't return an error. It returns Withington, because K05 is the largest code that isn't greater than K06. The ride gets another customer's area, and nothing on the sheet warns you. Add FALSE and you get #N/A, so at least you know something's missing.

Leave out FALSE and VLOOKUP often returns a believable answer for a code that doesn't exist. Always write the fourth argument, unless you really want a price band.

How often does that happen in real files? In a 2015 study (TU Delft), Felienne Hermans and colleagues at TU Delft counted 14,231 lookup functions in the Enron spreadsheets: 43% used an approximate match and 57% an exact one. Of those approximate lookups, 23% searched a range that wasn't sorted, and in 11 cases they found actual errors (Felienne Hermans).

MATCH, the other half of INDEX MATCH, has the same trap. Leave out its third argument and the default is 1, an approximate match that needs the values sorted in ascending order (Microsoft). So =INDEX(M2:M6,MATCH(B11,L2:L6)) returns Withington for K06 as well. XLOOKUP matches exactly by default (Microsoft), and that may be its biggest advantage.

An approximate match isn't always wrong, though. For price bands it's exactly what you want: at Flying Velo a ride from 0 km costs £6, from 3 km £9, and a ride of 7.2 km falls in the band from 6 km. A table like that is always sorted in ascending order, and then TRUE is the right choice. See how it works in approximate match with price bands.

What happens to VLOOKUP when you insert a column?

The column number ends up pointing at the wrong column. In =VLOOKUP(B2,L2:M6,2,FALSE), that 2 is a fixed number. Insert a column between L and M for a phone number, and Excel neatly turns the table into L2:N6, but the 2 stays a 2. The formula now returns whatever is in the new, empty column: a 0 instead of Manchester, and again without an error.

Microsoft listed this among VLOOKUP's weaknesses when it announced XLOOKUP, along with the approximate default and the fact that VLOOKUP can't look to the left (Microsoft). XLOOKUP and INDEX MATCH have no column number: they point at the range with the answers itself. After the insert, M2:M6 becomes N2:N6 by itself, and the formula still returns Manchester.

Can VLOOKUP look to the left?

No. VLOOKUP always searches the first column of the table and can only return what's to the right of it. On the Flying Velo sheet, the customer's name is in column K, to the left of the code in L. Kyle wants that name on every delivery slip, and VLOOKUP can't do it unless you shuffle the columns around. Then the column numbers in other formulas may no longer be right.

XLOOKUP looks wherever you tell it to. =XLOOKUP(B2,L2:L6,K2:K6) finds K03 among the codes and takes the name in the same position in K2:K6: Cygnet Café. =INDEX(K2:K6,MATCH(B2,L2:L6,0)) does the same, and works in versions without XLOOKUP too. Because you give the lookup range and the return range separately, it doesn't matter which of the two is on the left.

What if the code isn't on the list?

Then XLOOKUP returns #N/A, just like VLOOKUP with FALSE. The difference is XLOOKUP's fourth argument, if_not_found: the text to show when no match is found (Microsoft). With VLOOKUP you need a second function for that, such as IFERROR to catch error values. For ride R10 and the new customer K06, it looks like this:

=XLOOKUP(B11,L2:L6,M2:M6,"New customer")
=IFERROR(VLOOKUP(B11,L2:M6,2,FALSE),"New customer")

Now try it yourself, on Flying Velo's real sheet. You'll look up the name to the left of the code, fill the formula down to the last ride and catch the new customer. Don't forget the dollar signs: without them, the customer list slides down a row with every row you fill, and customers drop out of it.

Your formula ran in your own browser, and we also checked it on a second version of the sheet, with different rides. So any formula that looks up correctly counts, even if it doesn't look like ours, while typing the names in by hand won't get past it. The exercise shows formulas in your own dialect: English names with commas, or Dutch ones with semicolons.

Which versions of Excel have XLOOKUP?

Microsoft 365 and Excel 2021 have XLOOKUP; Excel 2016 and Excel 2019 don't (Microsoft). Microsoft announced the function in August 2019 and, in March 2020, made it available to all Office 365 subscribers in the Monthly Channel, with the Semi-Annual Channel following in July 2020 (Microsoft). XLOOKUP is also on the list of what's new in Excel 2021 (Microsoft).

Support for Office 2016 and 2019 ended on 14 October 2025 (Microsoft), and for Office 2021 on 13 October 2026 (Microsoft). The apps may keep working, just without security fixes. So you can still come across a colleague, a client or a school on Excel 2016 or 2019, and their Excel doesn't know XLOOKUP.

What happens if you open XLOOKUP in an older Excel?

Microsoft itself says you may come across a workbook with XLOOKUP in Excel 2016 or 2019 if someone else created it in a newer version (Microsoft). What happens next, Microsoft describes for newer functions in general: once an earlier version recalculates the sheet, they return a #NAME? error instead of their current results (Microsoft).

If you're sharing a file with someone whose version you don't know, use VLOOKUP with FALSE or INDEX MATCH. You don't need to convert old sheets either: when it announced XLOOKUP, Microsoft wrote that VLOOKUP and HLOOKUP will both continue to be supported (Microsoft).

Is INDEX MATCH better than XLOOKUP?

Not better, but it still earns its place in three cases: when someone opens the file in a version without XLOOKUP, when you look up a row and a column at once, and when your lookup value is very long. With a lookup value of more than 255 characters, VLOOKUP returns #VALUE!, and Microsoft itself suggests INDEX and MATCH as a workaround (Microsoft).

In a table with rows and columns, like Flying Velo's weight surcharge per class and zone, two MATCH functions find the row and the column, and INDEX returns what's where they cross: =INDEX(L14:N16,MATCH(Q13,K14:K16,0),MATCH(Q14,L13:N13,0)) returns £5 for a medium parcel to the Docks. You'll build that pair step by step in INDEX and MATCH.

Just don't forget the 0. Without it, MATCH uses an approximate match, with the same silent errors as VLOOKUP without FALSE. Microsoft's own lookup guide now points to XLOOKUP and XMATCH as improved versions (Microsoft), and XMATCH matches exactly by default (Microsoft).

Why do some Excel formulas use semicolons instead of commas?

Because the separator between arguments depends on your operating system's locale and your Excel settings, not on whether you're on Windows or a Mac. Microsoft names the comma and the semicolon as the two most common, and a formula with the wrong one breaks (Microsoft). In Excel set to another language, the function names are translated too, so a formula from a Dutch colleague can look quite different:

=XLOOKUP(B2,L2:L6,K2:K6,"New customer")
=X.ZOEKEN(B2;L2:L6;K2:K6;"Nieuwe klant")

In our course you pick which form you see, and every formula adapts, including the one in the exercise above. For the other formulas you'll meet in any language, such as SUMIF and IF, see the basic Excel formulas everyone should know. They follow the same rule: the names and separators change, the logic doesn't.

VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?

Use XLOOKUP when everyone who opens the file has a version with XLOOKUP. Otherwise, use INDEX MATCH, or VLOOKUP with FALSE when the answer is to the right of the code. If you're not sure which version your readers have, the older function is the safe choice. Here's how that works out case by case:

If you often look things up between two lists, you're doing what a database does with a JOIN: linking two tables on a code. From Excel to SQL shows how VLOOKUP translates, and the SQL course teaches it in linking tables with JOIN.

Lookups are best learned on a sheet you can see, with codes that don't exist and columns that get added. In module 5 of our Excel course you do just that at Flying Velo, from VLOOKUP to XLOOKUP in any direction. Excel practice exercises with explanations shows how the exercises work, and learn Excel for a new job shows how the course fits what you need at work.