VLOOKUP vs XLOOKUP Function – What’s the Difference?

VLOOKUP has long been the benchmark based on which a user’s Excel knowledge was judged.

You don’t know Excel if you can’t use VLOOKUP.

Then things improved, and VLOOKUP’s reign came to an end because of a newer and better function, XLOOKUP.

The Excel team considered years of feedback about VLOOKUP limitations, and when they finally released a better version in XLOOKUP, they made sure most of it was sorted.

In this article, I will make a strong case for why XLOOKUP is a much better function (of course) and explain the difference between VLOOKUP and XLOOKUP.

So buckle up as I compare these two functions and get technical.

Syntax of VLOOKUP and XLOOKUP

Below is the syntax of the VLOOKUP function:

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

Where:

• lookup_value – The value you want to search for (the lookup value)
• table_array – The range of cells that contains the data you want to search through. This is the table array
• col_index_num – The column number in the table from which to retrieve the value
• [range_lookup] – A logical value (TRUE or FALSE). TRUE finds an approximate match, while FALSE finds an exact match. If omitted, it defaults to TRUE (which is an approximate match)

And here is the syntax of the XLOOKUP function:

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

Where:

• lookup_value – The value you want to search for (lookup value)
• lookup_array – The range of cells where you want to look for the lookup_value.
• return_array – The range of cells from which to return the value.
• [if_not_found] – The value to return if the lookup_value is not found.
• [match_mode] – Specifies the type of match to perform:
  • 0: Exact match (default)
  • -1: Exact match or next smaller item
  • 1: Exact match or next larger item
  • 2: Wildcard match
• [search_mode] – Specifies the search mode:
  • 1: Search from first to last (default)
  • -1: Search from last to first
  • 2: Perform a binary search in ascending order
  • -2: Perform a binary search in descending order

Just by looking at the syntax, you may think VLOOKUP is easier to use. But this is one of the cases where more is actually better. With more arguments, XLOOKUP actually makes it easier to use the function, and it also gives it much-needed flexibility, which VLOOKUP lacks.

We will see how this plays out in the next section, where I will compare VLOOKUP and XLOOKUP using specific examples.

VLOOKUP vs. XLOOKUP – Differences

Let’s understand the difference between VLOOKUP and XLOOKUP by looking at some examples.

VLOOKUP Uses a Hardcoded Column Number, XLOOKUP Uses a Return Array

When using VLOOKUP, you need to specify the exact column number from which you want to extract the result.

With XLOOKUP, there is no need for column counting as you can specify the lookup_array and return_array separately.

Below, I have a data set where I have employee names, their ID, and their department name in three columns, and I want to fetch the department name for Gloria in column G.

With VLOOKUP, you can do this using the below formula:

=VLOOKUP(F2,$A$2:$C$15,3,FALSE)

And with XLOOKUP, you can do the same with the following formula:

=XLOOKUP(F3,A2:A15,C2:C15)

In the VLOOKUP formula, I need to specify the column number from where I want to fetch the value for the matching lookup value.

In this example, since the department name is in the third column of the dataset, I have to specify 3 as the third argument in the VLOOKUP formula.

On the contrary, with XLOOKUP, the lookup array and the return array are two separate arguments, so I don’t need to count columns and specify the one from where I want the result.

Instead, I can select the lookup array and the return array independently.

VLOOKUP’s hardcoded col_index_num can cause trouble when you insert or delete a column inside table_array. Insert a new column B here, and it silently returns Gloria’s ID, AI-817, instead of Marketing.

Deleting a column can also return the wrong column or give #REF!. XLOOKUP’s return_array is a range reference, so Excel adjusts it and the formula keeps returning Marketing.

VLOOKUP Always Looks Up in the Leftmost Column (XLOOKUP Doesn’t)

One of the biggest limitations of the VLOOKUP function is that it always searches for the lookup value in the first (left-most) column of your data range (the table_array argument).

This also means that you cannot look up and return a value from the left of the lookup column.

In contrast, with XLOOKUP, you can specify any column for the lookup, not just the first one. This also means that you can look up and return values from the left of the lookup column.

Below, I have a data set where I have employee names, their ID, and their department name in three columns, and I want to fetch the employee name for a given employee ID.

Unfortunately, this is not something you can do with VLOOKUP with the current construct of the data set.

This is because if you select the entire data set, the employee id will not be the leftmost column in the data set. If you select the table array starting from the Employee ID column, then you won’t be able to return the name as it won’t be a part of the table array in that case.

But this is not a problem for XLOOKUP.

The formula below will easily give me the result:

=XLOOKUP(E2,B2:B15,A2:A15)

XLOOKUP Defaults to Exact Match, VLOOKUP Defaults to Approximate Match

Another welcome improvement in the XLOOKUP function is that the match mode argument defaults to an exact match.

In VLOOKUP, if you don’t specify the match mode argument (called [range_lookup]), it defaults to approximate match, which is a less-used use case, and in most cases, users are looking for an exact match.

Below, I have a data set with students’ names in column A and their scores in column B, and I want to get the score of the student named Joseph in cell E2.

If I use the VLOOKUP function without specifying that I need an exact match, it will default to approximate match, giving me the wrong result.

=VLOOKUP(D2,A2:B15,2)

As you can see, the above VLOOKUP formula gives me a score of 72, while the actual result should be 68.

To get the right result, I will have to use the below VLOOKUP formula, where I need to specify the exact match mode (by using FALSE or 0 as the fourth argument).

=VLOOKUP(D2,A2:B15,2,FALSE)

Since XLOOKUP defaults to an exact match (in case the match mode argument is not specified), I can use the below XLOOKUP formula to get the right result:

=XLOOKUP(D2,A2:A15,B2:B15)

If you want to use an approximate match in XLOOKUP, you can specify the match mode separately (it’s the fifth argument).

Also read: VLOOKUP Vs. INDEX/MATCH – Which One is Better? (Answered)

XLOOKUP Can Look Up Values from Bottom to Top

When using VLOOKUP, it scans the lookup column starting from top to bottom and returns the corresponding value as soon as it finds a match.

In XLOOKUP, you can specify the direction of the search – which can be from first to last or last to first. If omitted, it would default to the commonly used first-to-last search (i.e., top to bottom in vertical lookup and left to right in horizontal lookup).

Below, I have a data set where I have department names in column A and their employee names in column B. I want to know the name of the last employee tagged as part of the marketing department.

While I cannot do this using the VLOOKUP formula, it can easily be done using the following XLOOKUP formula:

=XLOOKUP(D2,A2:A15,B2:B15,"",0,-1)

The above formula gives me the result as Minnie, who is the last employee name for the Marketing department in the list.

XLOOKUP Can Return Values From Multiple Columns

Since XLOOKUP is available in Excel versions that also have dynamic arrays, you can use it to return multiple lookup values from different columns.

VLOOKUP, on the other hand, is designed only to return one value in the standard format. While you can hack the formula to give you more than one result, you will find XLOOKUP to be a lot easier in such situations.

Below, I have a dataset where I have employee names, their employee ID, and their department names in three separate columns. I want to extract the employee id and their department name for the name in cell E2.

Let’s see how to do this using VLOOKUP.

I can do this using two separate formulas:

=VLOOKUP(E2,A2:C15,2,0)

and

=VLOOKUP(E2,A2:C15,3,0)

So I have entered one formula that gives me the value from column 2 and then the other formula that gives me the value from column 3.

And, if you have access to dynamic arrays, you can also use the formula below:

=VLOOKUP(E2,A2:C15,{2,3},0)

With XLOOKUP, you can do the same thing with the following formula:

=XLOOKUP(E2,A2:A15,B2:C15)

With XLOOKUP, I can specify the return array as a multiple-column range, and it will return the results from all the columns for the matching lookup value.

Also read: How to Use VLOOKUP with Multiple Criteria

XLOOKUP Can Handle Situations with Missing Values

Another welcome improvement in the XLOOKUP function is that it has an argument that allows you to specify what it should give you in case it doesn’t find the lookup value.

Below, I have a data set where I have employee names in column A and their employee ID in column B, and I want to get the employee ID for the name in cell D2. In case the formula is not able to find the name, I want it to return “Not Found”

The following formula will do this:

=XLOOKUP(D2,A2:A15,B2:B15,"Not Found")

In the above formula, I have specified “Not Found” as the fourth argument, which would be returned in case the formula is not able to find the lookup value.

If you want to do the same thing with the VLOOKUP function, you will have to use it along with IFERROR or IFNA functions:

=IFNA(VLOOKUP(D2,A2:B15,2,0),"Not Found")

XLOOKUP Approximate Match Doesn’t Need Data to be Sorted

VLOOKUP has two match modes – Exact match and Approximate match.

For the approximate match to work in VLOOKUP, your data needs to be sorted in ascending order.

With the XLOOKUP function, you get two approximate match modes:

• Exact match or the next smaller item
• Exact match or the next larger item

Also, while your data needs to be sorted in ascending order when using the approximate matching in VLOOKUP, XLOOKUP does not need sorted data with its default search mode. Its binary search modes (2 and -2) do require sorted data.

Below, I have a data set where I have student names in column A and their scores in column B, and I want to get their grades in column C based on the table on the right.

As you can see, the grades table is not sorted in ascending or descending order.

If I use the following VLOOKUP function with this data set, it is going to give me the wrong result.

=VLOOKUP(B2,$E$2:$F$7,2,TRUE)

This is understandable as the approximate match in VLOOKUP requires the table to be sorted in ascending order, and our grades table is not sorted.

But XLOOKUP can work with this unsorted table:

=XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7,,-1) 

In the above XLOOKUP formula, I have used -1 as the fifth argument (match_mode) to find the exact score or the next smaller score in E2:E7 and return that score’s grade from F2:F7.

XLOOKUP Has a Wildcard Character Match Mode Option

While VLOOKUP has only two match modes – Exact match and Approximate match, XLOOKUP has the following four match modes:

1. Exact match
2. Exact match or the next smaller item
3. Exact match or the next larger item
4. Wildcard character match

While I’ve already covered the first three match modes in the previous examples, another new one in XLOOKUP is the Wildcard character match.

With VLOOKUP in exact-match mode (FALSE or 0), wildcard characters in a text lookup value are automatically considered.

But with XLOOKUP, you need to explicitly specify whether you want the function to use wildcard characters as wildcards or not.

Let me explain with an example.

Below, I have student names in column A and scores in column B. Cell D2 holds Glo*, where the asterisk stands for any characters, and I want the score for the name that matches this pattern.

I can use the below VLOOKUP formula to do this:

=VLOOKUP(D2,A2:B15,2,0)

As you can see, this exact-match VLOOKUP formula treats * and ? as wildcards. A tilde (~) before either symbol makes it literal.

But see what happens when I use the below XLOOKUP formula:

=XLOOKUP(D2,A2:A15,B2:B15)

This gives me #N/A because XLOOKUP searches for the literal text in D2 by default, and that exact text is not in the list.

If I want XLOOKUP to consider the asterisk as a wildcard character, I can use the below formula:

=XLOOKUP(D2,A2:A15,B2:B15,,2)

Here, I have used 2 as the fifth argument, which makes XLOOKUP consider wildcard characters as wildcards.

So if you do not want wildcard matching, use XLOOKUP’s default exact-match mode, or put a tilde (~) before a literal * or ? in VLOOKUP’s lookup value.

Lookup Value Size Limit

The lookup value in VLOOKUP can be up to 255 characters long. XLOOKUP can handle longer lookup text, but Excel’s cell and formula limits still apply.

But this may not be an issue in most scenarios.

In case you’re working with long lookup values such as text, this could be an issue with VLOOKUP.

VLOOKUP and XLOOKUP Speed Depends on the Workbook

Neither VLOOKUP nor XLOOKUP is always faster. The result depends on your Excel version, the ranges and data you use, and the match and search modes.

If speed matters in a large workbook, compare calculation times using your actual data and equivalent lookup settings.

You can read more about the speed comparison of XLOOKUP and VLOOKUP here.

VLOOKUP is Compatible in All Excel Versions, XLOOKUP in New Versions Only

One obvious disadvantage of the new function is that it is not compatible with the older versions of Excel.

XLOOKUP is available in Microsoft 365, Excel 2021, Excel 2024, and Excel for the web. It is not available in Excel 2016 or Excel 2019.

This means that if you’re working with someone who’s using an older version of Excel, you’ll have to stick to using VLOOKUP (or make them upgrade).

I think this is a temporary issue as Microsoft slowly moves all the Excel users to Microsoft 365, where everyone would have access to all the new functions and functionalities.

In this article, I’ve covered how the VLOOKUP and XLOOKUP functions are different from each other, and all the improvements that have been made to the XLOOKUP function.

Difference	XLOOKUP	VLOOKUP
Column Specification	Uses lookup_array and return_array, no need for column counting	Requires specifying column number from which to return the value
Inserting or Deleting Columns	return_array reference adjusts automatically	Hardcoded column number can silently return the wrong column or #REF! after changes inside table_array
Lookup Column Position	The lookup column doesn’t need to be the leftmost column.	Only searches in the left-most column of the table array
Default Match Type	Defaults to exact match	Defaults to approximate match
Search Direction	Can search from first to last or last to first	Searches only from first to last (top to bottom)
Multiple Column Return	Can return values from multiple columns	Returns one column by default; an array of column numbers can return several columns in versions with dynamic arrays
Handling Missing Values	Can specify a value to return if the lookup value is not found ([if_not_found] argument)	Requires using IFERROR or IFNA functions to handle missing values
Approximate Match	Supports next smaller and next larger matches without sorting in the default search mode; binary search requires sorted data	Data must be sorted in ascending order for approximate match
Wildcard Characters	Supports wildcard character match mode explicitly	Uses wildcards in exact-match mode; a tilde escapes literal * and ?
Lookup Value Size Limit	Can handle lookup text longer than 255 characters, subject to Excel’s cell and formula limits	Lookup value can be up to 255 characters long
Speed	Speed depends on the workbook and lookup settings	Speed depends on the workbook and lookup settings
Compatibility	Available in Microsoft 365, Excel 2021, Excel 2024, and Excel for the web	Compatible with all versions of Excel

While the VLOOKUP function is still widely used by many Excel users, if you have access to the XLOOKUP function, it would be a good idea to learn and start using it (as it offers many advantages over its predecessor).

I hope you found this article helpful.

If you have any suggestions or comments for me, please let me know in the comments section.

Other Excel articles you may also find useful:

• Formula vs Function in Excel
• Excel Filter Function
• How to make VLOOKUP Case Sensitive
• Advanced Excel Functions and Formulas
• Use VLookup to Get the Last Number in a List in Excel
• Lookup and Return Values in an Entire Row/Column in Excel
• LOOKUP Function (Vector and Array Form)
• XMATCH Function in Excel