XLOOKUP Function in Google Sheets

The XLOOKUP function in Google Sheets is a powerful tool designed to make searching and retrieving information within your spreadsheets easier and more efficient. Unlike its predecessors (VLOOKUP and HLOOKUP), XLOOKUP offers versatility and simplicity, allowing you to search in any direction, return multiple results, and gracefully handle missing values. In this guide, we'll explore the syntax and practical examples to help you become proficient in using XLOOKUP.

XLOOKUP Function Syntax

The XLOOKUP function has a straightforward syntax; it requires a minimum of three arguments, with an additional three optional arguments. 

=XLOOKUP(search_key, lookup_range, return_range, [missing_value], [match_mode], [search_mode])

• search_key: The value to search for.

• lookup_range: The range where the search key will be found, either a single column or row. 

• return_range: The range from which to return a value. If a value is found in the lookup_range, its position corresponds to that same position in the return_range. 

• missing_value (optional): The value to return if the search key is not found. The default is `#N/A`.

• match_mode (optional): Specifies the match type. 

  • 0 - is for an exact match

  • 1 - is for an exact match or the next value that is greater than the search_key

  • -1 - is for an exact match or the next value that is lesser than the search_key

  • 2 - is for a wildcard match

• search_mode (optional): Specifies the search mode. 

  • 1 - is to search from the first entry to the last

  • -1 - is to search from the last entry to the first

  • 2 - is to search through the range with binary search. The range needs to be sorted in ascending order first

  • -2 is to search through the range with binary search. The range needs to be sorted in descending order first

XLOOKUP Function Examples

We'll explore some examples by utilising these mandatory and optional arguments.

Example 1: Basic Exact Match

It’s possible to use only the three main arguments (search_key, lookup_range and return_range) and return an exact match. Let’s assume we want to look for a salesperson’s name and return a value from the CloudTech column below. The formula would be like this:

=XLOOKUP(B14,B5:B10,C5:C10)

The search_key, in this case, is the salesperson’s name, which we are collecting from a dropdown selection. 

The lookup_range, in this case, is a single column where we expect to locate the search_key

The results_range, in this case, is a single column that we would like to retrieve a value from that corresponds to the same location as the lookup_range. In this example, the search_key is located in the 5th position in the lookup_range; therefore, the 5th value from the results_range is returned, which is 108 for James Williamson. 

Example 2: Missing Value

There will undoubtedly be a scenario where the search_key is not located within the lookup_range; therefore, we can specify what to return if the item is not found in the list.

By default, if we do not specify a missing_value and the search_key is not located within the lookup_range, then #N/A will be returned, which is ugly, as you can see below, so adding a condition to account for this will make things cleaner and easier to decipher. 

Adding this missing_value at the end of the function is optional; you can call it whatever you like, or nothing at all, so for example, both of these are legitimate missing_values:

=XLOOKUP(B14,B5:B10,C5:C10,"")

=XLOOKUP(B14,B5:B10,C5:C10,"Not found")

They will both perform the same purpose, but the second one is just more informative. The name Rita Orlando is not listed in the table, so returning nothing “ “ or “Not found” tells us something, whereas #N/A prompts investigation; therefore, it is always recommended to include a missing_value to account for the unknown. 

Example 3: XLOOKUP Lookup Range position

If you’re familiar with the VLOOKUP function in Excel or Google Sheets, you’ll know that one of its biggest flaws is that the lookup_range must always be positioned to the left of the results_range, but with the XLOOKUP, it doesn’t matter anymore. This is a major plus when you have large tables of data and the lookup_range can be somewhere in the middle or to the right, and you want to get a value from the left side of the table. 

With XLOOKUP, the position of the lookup_range and the results_range can be anywhere, but they do have to be the same size in terms of the height of the range or the number of rows. It is possible to have more than one column for the results_range, but more on this later. 

In this example above, the lookup_range is on the far right, and the result_range is on the far left, which would not be possible with VLOOKUP.

Example 4: Approximate Match

If you recall, the match_mode is optional, and when this is omitted, an exact match is returned, which is essentially a zero for the match_mode argument. 

• match_mode (optional): Specifies the match type. 

  • 0 - is for an exact match

  • 1 - is for an exact match or the next value that is greater than the search_key

  • -1 - is for an exact match or the next value that is lesser than the search_key

  • 2 - is for a wildcard match

In the example below, we’re using a -1 match_mode argument, which returns the next value that is lower than the search_key. You can see the search_key is 80; therefore the next lowest value is 79, hence William Green is returned since he has a value of 79.

=XLOOKUP(B14,F5:F10,B5:B10,"Not found",-1)

If we switch this to using a 1 match_mode argument, this returns the next value that is higher than the search_key. You can see the search_key is 80, just as before; therefore the next highest value is 114, hence Isabella Martinez is returned since she has a value of 114.

=XLOOKUP(B14,F5:F10,B5:B10,"Not found",1)

Example 5: Wildcard Match

The XLOOKUP function in Google Sheets supports three wildcards, *, ?, and ~ to use with the match_mode argument.

• The star * wildcard matches zero or more characters.

• The question mark ? wildcards matches exactly one character.

• The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.

Imagine we know the surname in this example, but we want to find the first name. We can use the XLOOKUP function with a slight modification to the search_key to return what we need. There is the modified formula below. You’ll notice that the search_key contains the surrounded by quotes, followed by the ampersand sign and lastly, the cell B14, like this: ""&B14

=XLOOKUP("*"&B14,B5:B10,B5:B10,"Not found",2)

This "*"&B14 essentially looks for Davis in the lookup_range and returns the name from the same range (result_range). As mentioned above, the * matches zero or more characters. This, of course, should be used with the match_mode of 2. 

It should be noted that if there were more than one possible result, only the first would be returned, so it is not an ideal solution. Honestly, I’ve not found an opportunity to use this as there is always a risk of inaccurate results; I would prefer to use the FILTER or QUERY functions to return a specific result as this allows for much more flexibility in the search criteria. 

Example 6: Return Multiple Results

The XLOOKUP function has a real superpower up its sleeve by being able to return multiple columns of results. This type of function is perfect for data analysis, amongst other possibilities. It is also possible to return multiple columns of results with the VLOOKUP function, but it’s not an out-of-the-box feature like the XLOOKUP function. 

With the XLOOKUP, everything remains the same; you simply specify a range of columns with the results_range as you can see in the example below. 

=XLOOKUP(B14,B5:B10,C5:F10,"Not found")

By specifying the range C5:F10 were asking the function to return all columns, and that’s exactly what you get. 

Example 7: Different Search Mode

The last argument for the XLOOKUP function allows you to alter the search technique employed. By default, it searches from the top to the bottom of your specified range, though you have the option to reverse this to bottom-to-top if it suits your needs better.

Additionally, XLOOKUP is capable of executing rapid binary searches, provided that your data is properly sorted to prevent inaccurate outcomes.

Conclusion

In this guide, we've only scratched the surface of what's possible with the XLOOKUP function in Google Sheets. By leveraging its full capabilities, you can streamline your data analysis and make your spreadsheets more dynamic and informative. Whether you're performing a basic exact match or handling more complex scenarios like wildcard matches and multiple results, XLOOKUP is a versatile tool that can enhance your productivity and data management in Google Sheets. 

If you want to play with the XLOOKUP function in Google Sheets you can begin by exploring the examples used in this post by making a copy of this XLOOKUP demo sheet here.

Stay tuned for more tutorials that will help you harness the full potential of Google Sheets to transform your data analysis and workflow automation.