When it comes to searching for data in Google Sheets, the new XLOOKUP function takes the crown. It surpasses the capabilities of older functions like VLOOKUP or HLOOKUP, providing more power and flexibility. If you want to become a search wizard in Google Sheets, the XLOOKUP function is your secret weapon.
What is XLOOKUP?
XLOOKUP is a powerful search function in Google Sheets. It searches for a lookup value within a search range and returns the corresponding value from the result range. If no match is found, you can specify a default value. Just like other search functions, you can control the match mode and even the search mode. Let’s dive into the details, but first, let’s take a look at a simple example.
Here’s a straightforward XLOOKUP formula that looks for a lookup value in column A and returns a value from column C:
=XLOOKUP(A2, A:C, C:C)Looks like this in the spreadsheet:
Find this example and more in the template at the end of this article.
Syntax of the XLOOKUP Function
The XLOOKUP function takes a minimum of three and a maximum of six arguments:
lookup_value: The value you want to search for.search_range: The range in which to search. It can be either a single column or a single row.result_range: The range to consider for the result. The return value is taken at the position of the matching value in the search range. The result range must have the same dimensions as the search range.[if_not_found]: The replacement value to return if no match is found. This is an optional argument, and if omitted, an error is returned if there is no match.[match_mode]: This optional argument allows you to specify the match mode to use. If not specified, an exact match is used.
The match mode options are as follows:
| Option | Match Mode Behavior |
|---|---|
| 0 | Exact Match |
| 1 | Exact Match or Next Larger Value |
| -1 | Exact Match or Next Smaller Value |
| 2 | Match with Wildcards |
| [search_mode] | This optional argument allows you to specify the search mode to use. If omitted, XLOOKUP searches from the first entry to the last entry in the search range.
The different search options are as follows:
| Option | Search Mode Behavior |
|---|---|
| 1 | Search from First Entry to Last Entry |
| -1 | Search from Last Entry to First Entry |
| 2 | Search through the range using binary search, assuming the range is sorted in ascending order |
| -2 | Search through the range using binary search, assuming the range is sorted in descending order |
Notes on the XLOOKUP Function
- The search range can only be a single column or a single row. It cannot be a range with multiple rows and columns.
- The result range must be compatible with the size of the search range. For example, if the search range is a column of data with 10 rows and 1 column, then the result range must also have 10 rows (although it can have more than one column).
Examples of the XLOOKUP Function
Let’s explore some additional examples of the XLOOKUP function in action in Google Sheets.
Example 1: Basic Exact Match
If you omit the optional match mode argument, the XLOOKUP function performs an exact match.
In other words, when you write it with only the first three arguments – a lookup value, a search range, and a result range – it looks for an exact match. We saw this in the example at the top of this page:
=XLOOKUP(A2, A:A, C:C)Which works like this in the spreadsheet:
Example 2: Handling Missing Values
Now, let’s specify a replacement value if no match is found. This is done with the fourth (optional) argument. For example:
=XLOOKUP(A2, A:A, C:C, "Missing Value")In our spreadsheet:
In this case, the lookup value “XYZ123” is not found in the search range (column A), so the XLOOKUP function returns the specified missing value, which we set as “Missing Value”.
Example 3: XLOOKUP to the Left
One advantage of the XLOOKUP function is that the search range does not need to be to the left of the result range, as is the case with the VLOOKUP function (although there is a complicated workaround with array lists).
The formula remains the same, but this time the result range is positioned to the left of our search range:
=XLOOKUP(A2, C:C, A:A)As you can see, it works just as well in our spreadsheet:
Example 4: Approximate Match
The fifth argument of the XLOOKUP function determines the match mode. If omitted or set to 0, an exact match is performed.
However, there are situations where the approximate match option works great.
Let’s consider a scenario of bank savings:
=XLOOKUP(B2, B:B, C:C, "", -1)The XLOOKUP formula for this example is:
=XLOOKUP(B2, B2:B6, C2:C6, "", -1)Notice the -1 as the last argument, which tells the function to look for an exact match, and if not found, return the lower value in the array.
In this example, it doesn’t find the exact $137,832, so it looks at the lower value in the array, which is $100,000. That is at position 3 in the search array, so it returns the value from the 3rd position in the result array, which is 1.25%.
One last thing to mention with this example is how the fourth argument is left blank. This is where we can specify a “missing value” when no match is found. However, it is not necessary here because we are using an approximate match anyway.
Example 5: Match with Wildcards
XLOOKUP in Google Sheets supports three wildcard characters: *, ?, and ~.
The asterisk * matches zero or more characters.
The question mark ? matches exactly one character.
The tilde ~ is an escape character that allows you to search for a * or ? instead of using them as wildcard characters.
Let’s see an example that uses a last name to find the full name:
=XLOOKUP("*Doe", A:A, B:B)And another example that uses a last name to return the transaction income from that row:
=XLOOKUP("*Doe", A:A, C:C)Both formulas can be seen in the following image, with the first one in cell B17 and the second one in cell B18:
There are two important things to notice with this formula:
- The lookup value is “Doe,” but to use it in the XLOOKUP function, we first add the asterisk wildcard character, which matches anything before “Doe.”
So it becomes *Doe.
Note that if there were multiple “Doe” in this data set, it could pose a problem. In that case, you might want to consider using the QUERY function or the FILTER function to return all “Doe” results.
- The match mode in the fifth argument is set to 2, indicating a wildcard search.
Example 6: Returning Multiple Results
The XLOOKUP function can return multiple results for a single match, not just a single result like the VLOOKUP function (although there is a workaround to return multiple columns with VLOOKUP).
XLOOKUP returns multiple results by specifying a result range with multiple columns (or rows if you are doing a horizontal search).
The formula is as follows:
=XLOOKUP(A2, A:A, B:C)It gives the result:
Example 7: Different Search Mode
The last argument allows you to change the search method used. By default, the search is performed from the top to the bottom of your range, but you can change it to search from the bottom to the top if it makes sense.
XLOOKUP can also perform lightning-fast binary searches, but it requires your data to be correctly sorted to avoid incorrect results.
XLOOKUP Function Template
Click here to open a read-only copy: Crawlan.com
Feel free to make a copy: File > Make a Copy…
If you are unable to access the template, it may be due to your Google Workspace account settings.
In that case, right-click on the link to open it in an incognito/private browsing window to view it.
The XLOOKUP function is part of the search function family in Google Sheets. You can read more about it in the Google documentation.
