There’s no denying the importance of understanding the differences between VLOOKUP and XLOOKUP in Google Sheets. It not only helps you choose the right function for your specific needs but also allows you to troubleshoot any formula-related issues. By grasping the pros and cons of each function, you can make more informed decisions about which one to use in your spreadsheets.
Before we dive into the details, it’s worth mentioning that the differences between VLOOKUP and XLOOKUP can vary between Microsoft Excel and Google Sheets. For the purpose of this tutorial, we’ll focus exclusively on their disparities in Google Sheets.
The Purpose of Lookup Functions in Spreadsheets
The primary purpose of all lookup functions is to find things in a range. VLOOKUP is designed for vertical lookup exclusively, while XLOOKUP can handle both vertical and horizontal lookup. For this article, we’ll compare them by focusing on their differences when used for vertical lookup. It’s worth noting that for horizontal lookup, there’s another function called HLOOKUP, similar to VLOOKUP.
Syntax Differences between VLOOKUP and XLOOKUP
VLOOKUP has been available in Google Sheets since its inception in 2006. In contrast, XLOOKUP is a relatively new function introduced in 2022. While both functions are capable of performing lookups, XLOOKUP is more powerful and boasts 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])
The only argument common to both functions is the search_key. VLOOKUP’s range and index arguments are replaced by lookup_range and result_range in XLOOKUP. Similarly, is_sorted is replaced by match_mode and search_mode. Additionally, XLOOKUP has the additional missing_value argument.
Let’s delve into how these changes affect the lookup data operation when we perform actual lookups using Google Sheets.
Advantages of Replacing Range and Index in VLOOKUP with Lookup_range and Result_range in XLOOKUP
One key difference between VLOOKUP and XLOOKUP lies in the way they handle lookup ranges. This distinction is crucial if you want to harness the full potential of these functions.
Figure 1
Let’s consider the above table, where we want to find the population of Tuvalu, one of the least populated countries in the world. With VLOOKUP, we can use the following 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. Since the first column is not sorted, we specify FALSE for an exact match.
Now, let’s see how XLOOKUP handles the same scenario:
=XLOOKUP("Tuvalu",B3:B7,C3:C7)
We replaced range with lookup_range and index with result_range. We don’t need to specify FALSE in XLOOKUP since it is the default mode (unsorted and exact match).
How Does XLOOKUP Excel over VLOOKUP Here?
XLOOKUP allows you to use any column as the lookup array, unlike VLOOKUP, which restricts you to using only the leftmost column. This added flexibility makes XLOOKUP a breeze to use in various situations.
For instance, let’s assume you have a table with country names in column B and their corresponding populations in column C. Instead of rearranging your data to fit VLOOKUP’s limitations, you can leverage XLOOKUP to find the country name for a given population with the following formula:
=XLOOKUP(12829,C3:C7,B3:B7)
In contrast, if you were to use VLOOKUP to achieve the same outcome, the formula would be more complex:
=VLOOKUP(12829,{C3:C7,B3:B7},2,FALSE)
Please note that the curly brackets in the VLOOKUP example combine the columns in the range. VLOOKUP can only search down the leftmost column, hence the need for these brackets to specify where to look for the desired value. Alternatively, you can replace the curly brackets in the example with the HSTACK function, like this: HSTACK(C3:C7,B3:B7).
Apart from its inability to perform left lookups, VLOOKUP suffers from another drawback: the formula breaks when columns are inserted into the range. This is because the index column number remains static and doesn’t adjust dynamically. XLOOKUP doesn’t have this issue since it uses the result_range argument, which allows the formula to adapt to changes in column positions.
Handling N/A Errors in VLOOKUP and XLOOKUP
Built-in IFNA is one of XLOOKUP’s advantages over VLOOKUP. It marks the second difference between these two functions.
VLOOKUP returns a #N/A error when the search key is not found in the first column of the range. Usually, we wrap VLOOKUP with an IFNA function to remove this error. However, XLOOKUP has IFNA built-in.
Figure 2
Let’s consider Figure 2 above. In both VLOOKUP and XLOOKUP, the search key “Australia” is not found in the first column of the range or the lookup range, respectively. Consequently, both functions return the #N/A error value. However, in XLOOKUP, we can replace this error value with the custom text “Not Found!”.
VLOOKUP vs XLOOKUP in an Unsorted Range: Differences and Similarities
VLOOKUP, when used in an unsorted range, lacks the ability to perform an approximate match. In contrast, XLOOKUP shines in this aspect.
To perform an unsorted approximate match, we set the match_mode argument to 0 or omit it altogether in XLOOKUP. Here are some examples of XLOOKUP formulas with different match_mode values:
Formula #1: =XLOOKUP(A9,A2:A6,B2:B6,,1)
In this formula, the search key, represented by the date in cell A9, isn’t found in the lookup range. Since the match_mode is 1, the formula matches the next largest date.
Formula #2: =XLOOKUP(A9,A2:A6,B2:B6,,-1)
Similar to the previous formula, the search key isn’t found in the lookup range. This time, however, the match_mode is set to -1, prompting the formula to match the next smallest date.
Formula #3: =VLOOKUP(A9,A2:B6,2,FALSE)
As expected, the VLOOKUP formula returns #N/A because it doesn’t support approximate matches in unsorted ranges.
Figure 3
Both VLOOKUP and XLOOKUP support wildcard matches in unsorted ranges. In XLOOKUP, you can specify match_mode #2 for wildcard matching, while VLOOKUP inherently supports wildcard matches.
Example (VLOOKUP): =VLOOKUP("Vatican*",B3:D7,2,FALSE)
Example (XLOOKUP): =XLOOKUP("Vatican*",B3:B7,C3:C7,,2)
Both formulas utilize wildcard matching and will find and return the population of “Vatican City” from the table above.
Another major difference between VLOOKUP and XLOOKUP in an unsorted range is their search direction. While VLOOKUP can only search from top to bottom, XLOOKUP has the ability to search in both directions – from top to bottom and bottom to top. You can determine the search direction with the search_mode argument, which can be set to 1 (top to bottom) or -1 (bottom to top).
Figure 4
VLOOKUP vs XLOOKUP in a Sorted Range: Differences and Similarities
In a sorted range, VLOOKUP necessitates sorting your data by the first column in ascending order (A-Z) to avoid incorrect results. XLOOKUP, on the other hand, works with lookup ranges sorted in either ascending or descending order. By specifying match_mode of 2 for ascending order or -2 for descending order, you can leverage XLOOKUP’s flexibility. This is another key difference between VLOOKUP and XLOOKUP.
In a sorted range, both VLOOKUP and XLOOKUP are capable of performing both exact and approximate matches.
In an ascending sorted range, both VLOOKUP and XLOOKUP match the next smallest value for an approximate match. Both functions return the closest match. However, XLOOKUP offers the option to return the next largest value as well.
Figure 5
In all cases, if there are multiple matches, the result will be from the row closest to the search key.
In an ascending sorted lookup range, VLOOKUP’s approximate match selects the last occurrence, while XLOOKUP’s approximate match selects the first occurrence.
Figure 6
In summary, here are the key differences between VLOOKUP and XLOOKUP in Google Sheets:
- XLOOKUP uses a lookup range and a result range, providing more flexibility compared to VLOOKUP’s reliance on the leftmost column.
- VLOOKUP requires an IFNA wrapper to handle #N/A errors, whereas XLOOKUP has this functionality built-in.
- XLOOKUP supports approximate matches in unsorted lookup ranges, while VLOOKUP does not.
- VLOOKUP only searches from top to bottom, while XLOOKUP can search in both directions.
- XLOOKUP’s approximate match works in both ascending and descending sorted lookup ranges, while VLOOKUP’s approximate match only works in ascending sorted lookup ranges.
- In an ascending sorted lookup range, XLOOKUP can match the next largest value, whereas VLOOKUP can only find the next smallest value.
- XLOOKUP can replace both HLOOKUP and VLOOKUP.
Crawlan.com
From this comparison of VLOOKUP and XLOOKUP, it’s evident that XLOOKUP is the superior option for lookups in Google Sheets.
Resource:
