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
This Tutorial Covers:
ToggleSyntax 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.
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 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.
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 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.
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, 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.
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, 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:
- Exact match
- Exact match or the next smaller item
- Exact match or the next larger item
- 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.
I can use the below VLOOKUP formula to do this:
=VLOOKUP(D2,A2:B15,2,0)
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)
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)
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.
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 |
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 | Designed to return one value, requires multiple functions for multiple columns |
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 | No need for data to be sorted, supports both next smaller and next larger item match modes | Data must be sorted in ascending order for approximate match |
Wildcard Characters | Supports wildcard character match mode explicitly | Considers wildcard characters automatically |
Lookup Value Size Limit | No limit on lookup value size | Lookup value can be up to 255 characters long |
Speed | Slower than VLOOKUP, especially with larger datasets | Generally faster, but speed decreases with larger datasets and more columns |
Compatibility | Available only in newer versions of Excel with Microsoft 365 | 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: