VLOOKUP vs XLOOKUP Function – What’s the Difference?

VLOOKUP has long been the benchmark based on which 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.

Click here to download the example file and follow along

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 FALSE (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 Harcoded Column Numbers to Return Value from, XLOOKUP Uses an Array

When using the lookup, 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.

Data set to fetch department using vlookup and xlookup

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

=VLOOKUP(F2,$A$2:$C$15,3,FALSE)
VLOOKUP formula to get department name

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

=XLOOKUP(F3,A2:A15,C2:C15)
XLOOKUP formula to get department name

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 Always Looks Up in the Left Most 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.

Data set to look up and return value from the left side

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.

Dataset showing approximate match example VLOOKUP Xlookup

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)
VLOOKUP Giving wrong result with approximate match

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)
VLOOKUP right result with exact match

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)
XLOOKUP defaults to exact match

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 Lookup 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.

Dataset to do bottom to top lookup

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)
XLOOKUP to do bottom to top lookup

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.

Dataset to Lookup values from multple columns

Let’s see how to do this using VLOOKUP.

I can do this using two separate formulas:

=VLOOKUP(E2,A2:C15,2,0)
VLOOKUP to get value from column 2

and

=VLOOKUP(E2,A2:C15,3,0)
VLOOKUP to get value from column 3

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)
VLOOKUP with two columns as a list

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

=XLOOKUP(E2,A2:A15,B2:C15)
XLOOKUP formula to return values from multiple columns

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”

Lookup value when not present in lookup table

The following formula will do this:

=XLOOKUP(D2,A2:A15,B2:B15,"Not Found")
XLOOKUP with not found argument

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, there is no need for your data to be sorted when using the approximate matching XLOOKUP.

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.

Lookup grades with approximate match

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

Table not sorted

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)
VLOOKUP giving wrong result
VLOOKUP Approximate match gives wrong result if the table is not sorted in ascending order

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) 
XLOOKUP gives right result

In the above XLOOKUP formula, I have used -1 as the fifth argument, which gives the exact match grade or the next smaller grade.

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, if you have a wildcard character in the lookup value, it will automatically be 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 a dataset where I have the student names in column A and their scores in column B, and I want to get the score of the student name in cell D2.

Dataset to lookup with wildcard character

I can use the below VLOOKUP formula to do this:

=VLOOKUP(D2,A2:B15,2,0)
VLOOKUP works with wildcard by default

As you can see, VLOOKUP is programmed to automatically consider wildcard characters (such as asterisk, question mark, or tilde).

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

=XLOOKUP(D2,A2:A15,B2:B15)
VLOOKUP doesnt work with wildcard by default

This gives me an error, as it is programmed to ignore wildcard characters unless specifically specified.

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)
XLOOKUP works with wildcard

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

So if you’re in a situation where you do not want your lookup formula to treat wildcard characters as wildcards, you can do that with XLOOKUP but not with VLOOKUP.

Lookup Value Size Limit

The lookup value in VLOOKUP can up to 255 characters long. However, there is no such limit on the lookup value when using XLOOKUP.

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 is Faster than XLOOKUP (Surprisingly)

With all the improvements made to XLOOKUP, you can expect the function to be faster than its predecessor, VLOOKUP.

However, based on multiple tests run by different people, it was found that XLOOKUP is slower than VLOOKUP.

One reason behind this could be that because XLOOKUP performs a lot more checks and also has more arguments to handle, it weighs down on the speed.

VLOOKUP tends to lose its speed advantage as the data set grows and more columns are added to the table.

Since VLOOKUP would need to process a lot of data when there are multiple columns (compared to XLOOKUP that can have the table array in return array specified separately), this can lower the speed gap between the two functions with large datasets with multiple columns.

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 function is only available in Excel with Microsoft 365.

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 VLOOKUP an XLOOKUP functions are different from each other, and all the improvements that have been made to the XLOOKUP function.

DifferenceXLOOKUPVLOOKUP
Column SpecificationUses lookup_array and return_array, no need for column countingRequires specifying column number from which to return the value
Lookup Column PositionThe lookup column doesn’t need to be the leftmost column. Only searches in the left-most column of the table array
Default Match TypeDefaults to exact matchDefaults to approximate match
Search DirectionCan search from first to last or last to firstSearches only from first to last (top to bottom)
Multiple Column ReturnCan return values from multiple columnsDesigned to return one value, requires multiple functions for multiple columns
Handling Missing ValuesCan 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 MatchNo need for data to be sorted, supports both next smaller and next larger item match modesData must be sorted in ascending order for approximate match
Wildcard CharactersSupports wildcard character match mode explicitlyConsiders wildcard characters automatically
Lookup Value Size LimitNo limit on lookup value sizeLookup value can be up to 255 characters long
SpeedSlower than VLOOKUP, especially with larger datasetsGenerally faster, but speed decreases with larger datasets and more columns
CompatibilityAvailable only in newer versions of Excel with Microsoft 365Compatible 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:

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.
Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster