VLOOKUP vs XLOOKUP: Which Should You Use?

The short answer to VLOOKUP vs XLOOKUP: use XLOOKUP when everyone who opens the file has Excel 2021, Excel 2024 or Microsoft 365, and use VLOOKUP when the file must work in older versions or a test asks for it. XLOOKUP is simpler and safer, but VLOOKUP runs everywhere. This comparison is for anyone who knows one of the two and wants to know when to switch, especially if you have an Excel test coming up.

VLOOKUP vs XLOOKUP at a glance#

XLOOKUP fixes almost every weakness of VLOOKUP, at the cost of needing a newer version of Excel.

How the syntax differs#

VLOOKUP asks for a whole table and a column number. XLOOKUP asks for two separate ranges: where to look and what to bring back.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

With VLOOKUP, the lookup values must sit in the first column of table_array, and col_index_num counts columns from that first column. With XLOOKUP, lookup_array and return_array can be any two columns of the same height, in any order.

XLOOKUP has three optional arguments:

• if_not_found: text or a value to show instead of #N/A, for example "Not found".
• match_mode: 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match.
• search_mode: 1 first to last (the default), -1 last to first, plus 2 and -2 for binary search on sorted data.

The same lookup, side by side#

The clearest way to compare them is to solve the same task on the same data. Here is a small staff list:

Task 1: find a salary by ID (a right lookup)#

With the ID 104 in F2, both functions return 46,900:

=VLOOKUP(F2,A2:D6,4,FALSE)
=XLOOKUP(F2,A2:A6,D2:D6)

VLOOKUP needs the column number 4 and FALSE. XLOOKUP just points at the ID column and the salary column, and it uses an exact match by default.

Task 2: find an ID by name (a left lookup)#

With the name Aisha in F2, you want her ID from column A. The ID column is to the left of the name column, so VLOOKUP cannot do it without rearranging the data. XLOOKUP can:

=XLOOKUP(F2,B2:B6,A2:A6)

The result is 103. Before XLOOKUP, the usual answer was INDEX and MATCH; our comparison of VLOOKUP vs INDEX MATCH explains how that works.

Task 3: handle a name that isn't there#

If F2 holds Sam, both functions fail to find a match. VLOOKUP returns #N/A unless you wrap it. XLOOKUP has the message built in:

=IFERROR(VLOOKUP(F2,B2:D6,3,FALSE),"Not found")
=XLOOKUP(F2,B2:B6,D2:D6,"Not found")

Both show Not found. The XLOOKUP version is shorter, and it only catches the "no match" case, so a real problem such as a broken reference still shows up.

When to use VLOOKUP#

Use VLOOKUP when compatibility matters more than convenience. It is the right choice when:

• The file goes to people on older Excel. Excel 2019, 2016 and earlier don't have XLOOKUP. They show #NAME? instead of a result.
• A job test asks for VLOOKUP by name. Many tests still do, because it is the classic lookup skill. Our guide on how to pass an Excel test for a job interview covers what else these tests check.
• You're editing an existing workbook full of VLOOKUPs. Keeping one style makes the file easier for colleagues to follow.
• The lookup is simple and to the right, the table won't change shape, and you lock the range with dollar signs.

If you are new to it, the VLOOKUP function guide covers each argument with examples.

When to use XLOOKUP#

Use XLOOKUP for new work whenever everyone has Excel 2021, Excel 2024 or Microsoft 365. It is the better choice when:

• You need to look left, such as finding an ID from a name.
• Columns may be inserted or deleted. XLOOKUP references the return column directly, so it keeps pointing at the right data.
• You want the last match, such as the most recent order for a customer. Use search_mode -1.
• You want a clean "not found" message without nesting IFERROR.
• You want several columns back at once. =XLOOKUP(F2,A2:A6,B2:D6) returns name, department and salary in one formula.

The XLOOKUP function guide has more examples, including wildcard matches. Analyst roles increasingly expect it; see the Excel skills every data analyst needs.

Common mistakes with VLOOKUP and XLOOKUP#

Most lookup errors come from a few habits that are easy to fix:

1. Leaving out FALSE in VLOOKUP. Without it, VLOOKUP uses approximate match and can return the wrong row on unsorted data, with no error to warn you.
2. Counting the column from column A of the sheet. VLOOKUP counts from the first column of table_array, not from the start of the sheet.
3. Ranges of different heights in XLOOKUP. =XLOOKUP(F2,A2:A6,D2:D7) returns #VALUE! because the lookup and return ranges must be the same size.
4. Not locking ranges before copying down. Use $A$2:$A$6 (press F4 on Windows or ⌘T on a Mac) so the ranges don't slide down as you fill with Ctrl+D or ⌘D.
5. Sending an XLOOKUP file to someone on Excel 2019. They will see #NAME?. Check versions before you share.
6. Expecting a case-sensitive match. Neither function is case-sensitive: "aisha" matches "Aisha".

Is XLOOKUP faster than VLOOKUP?#

For the small and medium tables used in everyday work and job tests, you won't notice a speed difference. Choose based on compatibility and clarity, not speed. On very large sorted data, XLOOKUP's binary search mode (search_mode 2) can help, but the data must be sorted for it to return correct results.

If you also use INDEX MATCH, our guide to INDEX MATCH vs XLOOKUP explains whether it is still worth learning. The same "old function vs newer, more flexible one" question comes up with SUMIF vs SUMIFS.

Our recommendation#

Learn VLOOKUP first, then switch to XLOOKUP for everyday work. VLOOKUP is still the lookup most job tests ask for, and it works in every version of Excel, so you need to be able to write it quickly and correctly. Once it makes sense, XLOOKUP takes minutes to learn, and it is the better default for new files on Microsoft 365, Excel 2021 or Excel 2024.

In short: XLOOKUP for new files on modern Excel, VLOOKUP for compatibility and tests that name it.

Practise it#

Try the XLOOKUP exercise on this page: it is a left lookup that VLOOKUP cannot do. Then practise VLOOKUP exact match in a live spreadsheet and work through the XLOOKUP lesson to get fast with both before your test.