VLOOKUP vs XLOOKUP: which should you use?

VLOOKUP has been the go-to lookup function in Excel for years, but XLOOKUP arrived to do the same job with more flexibility and fewer errors. This comparison shows where they differ, which version you need, the common mistakes you make with VLOOKUP, and when you're better off sticking with VLOOKUP.

Choose XLOOKUP if your Excel supports it, otherwise VLOOKUP

In short: use XLOOKUP when you're on Microsoft 365, Excel 2021 or Excel 2024. The function is easier to build, doesn't break when you insert columns and searches in both directions. If you're on Excel 2019 or earlier, XLOOKUP isn't available and VLOOKUP (or INDEX with MATCH) remains the logical choice.

Both functions do the same groundwork: they look up a value in a list and return an associated value. The difference lies in convenience, direction and error resilience. Anyone who works with lookup formulas daily will notice that XLOOKUP requires less maintenance as a workbook grows.

You don't need to rebuild an existing workbook straight away. VLOOKUP continues to work; Microsoft isn't removing the function. If you're creating new formulas and have a version that supports XLOOKUP, it's wise to learn the new function because you'll avoid the classic VLOOKUP pitfalls. In a mixed team where not everyone runs the same Excel version, compatibility outweighs convenience and you stick with VLOOKUP for the time being.

VLOOKUP searches from left to right with a fixed column number

VLOOKUP (vertical lookup) scans the leftmost column of a range and returns a value from a column to the right. The syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: what you're searching for, e.g. an article number.
• table_array: the range to search; the lookup column must be the first column.
• col_index_num: the number of the column containing the answer, counting from the lookup column.
• range_lookup: FALSE or 0 for an exact match, TRUE or 1 for an approximate match.

Pay attention to the last argument. If you omit it, Excel defaults to TRUE and searches for an approximate match, which gives wrong results on unsorted data. For a reliable outcome you almost always type FALSE. An example that looks up the price for an article number in A2:

=VLOOKUP(A2, Artikelen!A:D, 4, FALSE)

The weak spot is that fixed number 4. If you insert a column inside the table_array, the formula still counts to 4 and suddenly fetches the wrong column, or returns a #REF! error. How to fix such a reference error is explained in solving the #REF! error.

XLOOKUP works with separate lookup and return arrays in any direction

XLOOKUP separates the column you search from the column you get back. So column order no longer matters. The syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_array: the column or row you search.
• return_array: the column or row from which the answer comes; it may be to the left or right.
• if_not_found: the text or value that appears if nothing is found, built in so you don't need to nest IFERROR.
• match_mode: 0 for exact (the default), -1 or 1 for the next smaller or larger value, 2 for wildcard characters.
• search_mode: 1 top to bottom, -1 bottom to top, 2 or -2 for binary search on sorted data.

The same price lookup becomes shorter and more robust:

=XLOOKUP(A2, Artikelen!A:A, Artikelen!D:D, "not found")

Because you refer to the columns themselves, the formula stays correct if you later insert a column. And with the fourth argument, you catch a missing value straight away.

The match mode and search mode give XLOOKUP extra power

The last two arguments of XLOOKUP enable exceptions that are tricky with VLOOKUP. Match mode determines how strict the matching is:

• 0: exact match, the default.
• -1: the next smaller value, useful for a tiered discount or tariff threshold.
• 1: the next larger value.
• 2: wildcard characters, where * stands for multiple characters and ? for a single character.

An example with a discount tier where you look up the discount for an ordered quantity, without the exact quantity being in the table:

=XLOOKUP(D2, Staffel!A:A, Staffel!B:B, 0, -1)

Search mode determines the direction. With 1, XLOOKUP searches top to bottom, with -1 bottom to top. The latter is useful when you want the last rather than the first occurrence, for instance the most recent price in a chronological list.

The main differences side by side

This table sums up the practical differences that determine which function feels easier to work with.

Feature	VLOOKUP	XLOOKUP
Search direction	Only to the right from the first column	Left, right, up and down
Default match	Approximate (TRUE)	Exact (0)
Inserting columns	Breaks because of the fixed index number	Keeps working
Handling not found	Nest IFERROR or IFNA	Built-in fourth argument
Returning multiple columns	No, one column per formula	Yes, returns a range that spills
Search from bottom to top	No	Yes, with search mode -1
Horizontal lookup	Separate function HLOOKUP needed	Same function
Available from	All modern versions	Microsoft 365, Excel 2021, Excel 2024

XLOOKUP only works from Excel 2021 and Microsoft 365

Availability is often the deciding factor. XLOOKUP was added with dynamic arrays and works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. In Excel 2019, 2016 and earlier the function is absent; if you open a file containing XLOOKUP there, you'll see a #NAME? error because Excel doesn't recognise the function name.

If you work in a team with different Excel versions, bear this in mind. A file with XLOOKUP opens fine on a colleague's Excel 2016, but the formulas don't calculate and show an error. In such a mixed environment, VLOOKUP or the combination INDEX with MATCH is the safe choice. XLOOKUP also requires an activated, current licence; how activation exactly works is explained in what software activation is and how it works.

INDEX with MATCH is the alternative for older versions

If you can't use XLOOKUP but still want to search to the left or be immune to column shifts, use INDEX with MATCH. MATCH finds the position of the lookup value and INDEX retrieves the desired value at that position.

=INDEX(Artikelen!D:D, MATCH(A2, Artikelen!A:A, 0))

The 0 in MATCH forces an exact match, just like FALSE with VLOOKUP. This combination works in all Excel versions and, unlike VLOOKUP, can also look to the left. The downside is that the formula is longer and less readable than XLOOKUP, which is why XLOOKUP became the successor.

VLOOKUP often goes wrong in the same ways

If VLOOKUP returns a wrong or empty result, the cause is usually one of these points.

• Approximate instead of exact. Without FALSE or 0, the function finds the nearest value. On unsorted data this delivers random outcomes.
• Lookup value not in the first column. VLOOKUP only looks in the leftmost column of the table_array. If the lookup column is further to the right, it finds nothing.
• Number versus text. An article number stored as a number in one list and as text in the other won't match. Make both the same.
• Spaces. An invisible leading or trailing space prevents a match. The TRIM function clears those up.
• Forgotten absolute reference. If you drag the formula without locking the table_array with dollar signs ($A$2:$D$500), the range shifts.

Many of these pitfalls disappear with XLOOKUP, which defaults to exact search and names the lookup and return columns separately.

XLOOKUP also replaces HLOOKUP

In the past you needed a separate function for horizontal lookups: HLOOKUP searched the top row and returned a value from a row below. XLOOKUP does that with the same syntax, only now you point to a row instead of a column. If the months are in row 1 and the turnover in row 2, you retrieve the turnover for a month like this:

=XLOOKUP("March", A1:L1, A2:L2)

So you don't need to learn a second function. Both vertical and horizontal lookups run through XLOOKUP, which reduces the number of functions you have to remember. HLOOKUP remains for older files, just like VLOOKUP.

On large worksheets, pay attention to calculation method

With lists of tens of thousands of rows, speed plays a role. VLOOKUP with an approximate match (TRUE) on sorted data is fast, because Excel then uses a binary search method. You get the same speed with XLOOKUP using search mode 2 or -2, which also performs a binary search on ascending or descending sorted data.

If you work with exact matches on unsorted data, the function scans the list row by row; that is comparable for both functions. A bigger gain is made by not referring to entire columns (A:A) but to a bounded range or an Excel table column, so that Excel doesn't process over a million empty rows in the calculation.

If you repeat the same lookup hundreds of times in a column, every unnecessary calculation adds up. Avoid nesting multiple heavy XLOOKUP or VLOOKUP formulas inside a single cell when you could use a single lookup and refer to the result. That keeps recalculation of a large workbook noticeably faster.

A practical example shows the difference in behaviour

Suppose you have a price list with article numbers in column A, names in column B and prices in column C. You want to retrieve the name for an article number.

With VLOOKUP that works fine, because the name is to the right of the lookup column:

=VLOOKUP(F2, A:C, 2, FALSE)

If you reverse the task and want to get the article number for a name, VLOOKUP gets stuck because the answer is left of the lookup column. XLOOKUP has no problem with that:

=XLOOKUP(F2, B:B, A:A)

If you want both the name and the price in one go, you give XLOOKUP a return_array of multiple columns. The answer automatically spills into the adjacent cells:

=XLOOKUP(F2, A:A, B:C)

That spilling can cause a #SPILL! error if neighbouring cells are occupied. So make sure the cells to the right of your formula are empty when you expect a multi-column result.

Choose based on version, direction and collaboration

Use these rules of thumb to decide quickly.

• Choose XLOOKUP if you have Microsoft 365, Excel 2021 or Excel 2024 and the rest of the team does too. You get shorter formulas, exact search as default and the answer may be on either side.
• Choose VLOOKUP if the file will also be opened in Excel 2019 or earlier, or if you maintain an existing workbook where VLOOKUP is already used.
• Choose INDEX with MATCH if you are on an older version but still want to search to the left; that combination does what XLOOKUP does but works in all versions.

Still unsure? Look at what you exactly need. If you look up a single value based on a single key and the answer is to the right, VLOOKUP is perfectly adequate. If you want to search to the left, return several columns at once, grab the last instead of the first match, or neatly catch a missing value, XLOOKUP is the logical choice. For combining two lists on a shared key, both suffice; the readability of your formula then tips the balance.

If you want to clean or summarise the source table before looking up, a pivot table is often handier than a string of separate formulas; see how to create a pivot table in Excel. The official description of both functions can be found in Microsoft documentation on XLOOKUP.