There are a few reasons why you might want to know the differences between VLOOKUP and XLOOKUP in Google Sheets. Here are the most important ones:
- It can help you choose the correct function for your specific needs.
- Knowing the differences between VLOOKUP and XLOOKUP can help you troubleshoot problems with your formulas.
- Finally, by understanding the pros and cons of each function, you can make more informed decisions about which one to use in your spreadsheets.
Before we start, it’s important to note that the differences between VLOOKUP and XLOOKUP may differ in Microsoft Excel and Google Sheets. They may vary slightly from application to application. This tutorial will focus only on how they differ in Google Sheets.
In addition to pointing out the key differences between VLOOKUP and XLOOKUP, I will also compare their arguments. This will help to highlight the key differences automatically.
Purpose of Lookup Functions in Spreadsheets
The purpose of all lookup functions is to find things in a range.
VLOOKUP is for vertical lookup only, while XLOOKUP can handle both vertical and horizontal lookup. Therefore, we will compare them by focusing on their differences when used for vertical lookup.
For horizontal lookup, there is another function similar to VLOOKUP called HLOOKUP.
VLOOKUP and XLOOKUP: Arguments Differences at a Glance
As far as I know, the VLOOKUP function has been available in Google Sheets since its initial release in 2006. However, XLOOKUP is a newer function introduced in 2022. While both functions are capable of performing lookups, XLOOKUP is more robust and has a more flexible syntax.
Here are the syntaxes of both functions.
VLOOKUP Syntax: VLOOKUP(search_key, range, index, [is_sorted])
XLOOKUP Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Only the search_key argument is common to both functions. VLOOKUP’s range and index arguments are replaced by lookup_range and result_range in XLOOKUP. is_sorted is replaced by match_mode and search_mode, and XLOOKUP additionally has the missing_value argument.
Let me explain how these changes affect the lookup data operation when we look up data in real life using Google Sheets.
Advantages of Replacing Range and Index in VLOOKUP with Lookup_range and Result_range in XLOOKUP
Here is one of the key differences between VLOOKUP and XLOOKUP. This is essential to understand if you want to go deep into the functions.
In the above table, to find the population of Tuvalu, one of the least populated countries in the world, we can use the following XLOOKUP formula.
=VLOOKUP("Tuvalu",B3:D7,2,FALSE)VLOOKUP searches for the lookup value “Tuvalu” in the first column of the range B3:D7 and returns the population from the second column (index 2) of the matching row. The first column is not sorted, so we specified FALSE.
Let’s see how XLOOKUP handles this.
=XLOOKUP("Tuvalu",B3:B7,C3:C7)We replaced range with lookup_range and index with result_range. We do not need to specify FALSE in XLOOKUP since it is by default FALSE (unsorted and exact match).
How Does XLOOKUP Win over VLOOKUP Here?
XLOOKUP allows you to use any column as the lookup array, not just the leftmost column. This gives you more flexibility and makes it easier to use XLOOKUP in a variety of situations.
For example, let’s say you have a table of data with the population of countries in column C and the country names in column B. (Please scroll up to see Figure 1.)
You can use XLOOKUP to find the country name for a given population by using the following formula:
=XLOOKUP(12829,C3:C7,B3:B7)In contrast, VLOOKUP only allows you to use the leftmost column as the lookup range. So, to find the country name for a given population using VLOOKUP, you would need to use the following formula:
=VLOOKUP(12829,{C3:C7,B3:B7},2,FALSE).This formula uses curly brackets to combine the columns in the range. VLOOKUP can only search down the leftmost column, so this is necessary to tell VLOOKUP where to look for the value you are searching for.
Note: You can also replace the curly brackets in the example with the HSTACK function as follows: HSTACK(C3:C7,B3:B7)
Other than the inability to lookup left, VLOOKUP has another drawback: the chance of breaking the formula when columns are inserted into the range. This is because the index column number is static and will not adjust dynamically. XLOOKUP does not have this issue because it uses the result_range argument, which allows the formula to adjust to changes in the column position.
Handling N/A Errors in VLOOKUP and XLOOKUP
Built-in IFNA is one of the advantages of XLOOKUP. It is the second difference between the VLOOKUP and XLOOKUP functions.
The VLOOKUP function returns a #N/A error when the search key is not found in the first column of the range. We usually remove it with an IFNA wrapper. However, XLOOKUP has IFNA built-in.
Please see Figure 2 below. The search key “Australia” is not found in the first column of the range in VLOOKUP and in the lookup range in XLOOKUP. Therefore, both functions return the #N/A error value. I have replaced that error value with the custom text “Not Found!”.
VLOOKUP vs XLOOKUP in an Unsorted Range: Differences and Similarities
As far as I am concerned, the real advancement in the XLOOKUP function is replacing the is_sorted argument with the match_mode and search_mode arguments. It is a broad and complex topic. Let me try to simplify it.
XLOOKUP Match Modes and VLOOKUP’s Default Match Mode Comparison
The VLOOKUP function in Google Sheets does not offer an approximate match in an unsorted range.
In the above VLOOKUP formulas, we set the is_sorted argument to FALSE to look up an unsorted range. This ensures an exact match of the search key.
In XLOOKUP, we can either specify 0 or omit to enter any value in match_mode for an exact match of the search key, and that’s what we have done with the examples above.
Another similarity between VLOOKUP and XLOOKUP in an unsorted range is the wildcard character match. In XLOOKUP, we can specify match_mode # 2 to perform a wildcard match. VLOOKUP supports wildcard matches by default.
Example (VLOOKUP): =VLOOKUP("Vatican*",B3:D7,2,FALSE)
Example (XLOOKUP): =XLOOKUP("Vatican*",B3:B7,C3:C7,,2)
Both formulas use wildcard matching and will match “Vatican City” in the above table and return its population.
Now, here are the differences between VLOOKUP and XLOOKUP in an unsorted range.
Other than 0 (exact match) and 2 (wildcard match), XLOOKUP has other match modes that can be used in unsorted lookup_ranges. They are 1 and -1, and they can be used to find the next largest value and the next smallest value, respectively.
Here are some examples:
Formula#1 (B9):
=XLOOKUP(A9,A2:A6,B2:B6,,1)The search key, i.e., the date in A9, is not found in the lookup range. Since the match mode is 1, the formula matches the next largest date.
Formula#2 (B11):
=XLOOKUP(A9,A2:A6,B2:B6,,-1)The search key, i.e., the date in A9, is not found in the lookup range. Since the match mode is -1, the formula matches the next smallest date.
Formula#3 (B13):
=VLOOKUP(A9,A2:B6,2,FALSE)Needless to say, the VLOOKUP function returns #N/A because it does not support approximate matches in unsorted ranges.
Missing Bottom to Top Search in VLOOKUP
The other major difference between VLOOKUP and XLOOKUP is that VLOOKUP can only search from top to bottom, while XLOOKUP can search in both directions, from top to bottom and bottom to top.
Assume you have multiple occurrences of search keys in the first column of your range. VLOOKUP will only match the first occurrence, but XLOOKUP can match the first or last occurrence, depending on the value of the search_mode argument. The search_mode argument can be 1 (top to bottom) or -1 (bottom to top).
VLOOKUP vs XLOOKUP in a Sorted Range: Differences and Similarities
If you specify is_sorted to TRUE in VLOOKUP, you must sort your data in ascending order by column 1 in the range. In other words, the first column of your range must be sorted in A-Z order to avoid inaccurate results.
On the contrary, XLOOKUP works with lookup_range sorted in ascending or descending order. You can specify either binary search 2 (A-Z order) or -2 (Z-A order) in the search_mode argument. This is another key difference between VLOOKUP and XLOOKUP functions.
In a sorted range, both VLOOKUP and XLOOKUP are capable of exact match or approximate match.
In an A-Z sorted range, VLOOKUP matches the last occurrence in the exact match, but XLOOKUP matches the first occurrence.
Data Sorted in Ascending Order:
In an ascending sorted range, both VLOOKUP and XLOOKUP match the next smallest value in an approximate match. Both will return the closest match. However, XLOOKUP has the option to return the next largest value as well.
Data Sorted in Ascending Order:
In all cases, if there are multiple matches, the result will be from the row closest to the search key.
The above are the other differences and similarities between VLOOKUP and XLOOKUP in Google Sheets.
Finally, XLOOKUP can also work in a lookup_range sorted in descending order. To do this, specify -2 in the search_mode argument.
In a descending sorted range, XLOOKUP will match the last occurrence of the search value in an exact match. For an approximate match, the result will be from the row closest to the search key.
Is There Still a Reason to Use VLOOKUP Instead of XLOOKUP?
We have gone through several formula examples to understand the key differences between VLOOKUP and XLOOKUP in Google Sheets. In all comparisons, XLOOKUP is the clear winner.
However, there is one situation where VLOOKUP still has an edge: it can return 2D array results, while XLOOKUP cannot (at least not yet).
In the following example, VLOOKUP takes two lookup values (search keys) and returns a 2D array result. It uses two index numbers.
=ARRAYFORMULA(VLOOKUP(A11:A12,B3:D7,{2,3},FALSE))
The XLOOKUP return array is one-dimensional. If you try to use it to return a 2D array, you will only get the results from the first column or first row of the result range.
The following formula will return all values from the first column of the result range from the matching found rows:
=ARRAYFORMULA(XLOOKUP(A11:A12,B3:B7,C3:D7))Result: {10873;38786}
But I have an XMATCH and CHOOSEROWS workaround for this problem in my tutorial titled “CHOOSEROWS function in Google Sheets.”
Conclusion
In short, here are the key differences between VLOOKUP and XLOOKUP in Google Sheets:
- XLOOKUP uses a lookup range and a result range. This is more flexible than VLOOKUP’s range and index, which only allows you to search down the leftmost column.
- VLOOKUP requires an IFNA wrapper to remove the #N/A error, while XLOOKUP has this functionality built in.
- XLOOKUP supports an approximate match of the search key in an unsorted range. VLOOKUP does not have this functionality.
- VLOOKUP searches from top to bottom, while XLOOKUP can search from top to bottom and bottom to top.
- XLOOKUP’s approximate match works in both ascending (A-Z) and descending (Z-A) sorted lookup ranges. VLOOKUP’s approximate match only works in ascending sorted lookup ranges.
- In an ascending sorted lookup range, when using an approximate match, VLOOKUP matches the next smallest value. XLOOKUP also does this, but it can also match the next largest value.
- XLOOKUP can replace HLOOKUP and VLOOKUP.
From my comparison of VLOOKUP and XLOOKUP, you can clearly understand why XLOOKUP is a better option for lookups in Google Sheets.
Resources:
