The XLOOKUP function in Google Sheets gives you an easy way to find the data you’re looking for quickly. XLOOKUP doesn’t have the same limitations as VLOOKUP and HLOOKUP, enabling you to perform lookups in any direction.
If you’re a Microsoft Excel user, you may have used XLOOKUP there. Luckily, it works the same way in Google Sheets. Whether you’re used to the function in Excel or brand new to it altogether, we’ll show you how to use XLOOKUP to find specific values from a range of cells.
About XLOOKUP in Google Sheets
With the XLOOKUP function and accompanying formula, you can perform a lookup in one cell range and return a matching result from another. This is handy for sheets that contain a lot of data where using your eyeballs is time-consuming.
The syntax for the function is XLOOKUP(search_value, lookup_range, result_range, missing_value, match_mode, search_mode).The first three arguments are required. The three remaining arguments can be used to customize your lookup.
Search_value: The value to look up which can be a number, text, or cell reference. Text should be placed within quotation marks. Lookup_range: The cell range to look for the search_value which should be a single row or column. Result_range: The cell range to look for the result that corresponds to the search_value which should be the same size as the lookup_range. Missing_value: The value to return if there’s no match to the search_value. The formula returns the #N/A error by default. Match_mode: How to find the matching search_value. Enter 0 for an exact match, 1 for an exact match or the next value greater than the search_value, -1 for an exact match or next value less than the search_value, or 2 for a wildcard match. The default is 0. Search_mode: How to search the lookup_range. Enter 1 to search from the first to the last entry, -1 to search from the last to the first entry, 2 to use a binary search with values in ascending order, or -2 to use a binary search with values in descending order. The default is 1.
How to Use XLOOKUP in Google Sheets
To show how the function works, we’ll start with a simple lookup using the required arguments and then move on to additional examples that use the optional arguments.
RELATED: How to Find Data in Google Sheets with VLOOKUP
Here, we have a sheet of customer orders that includes contact details and order information. For the first example, we’ll do a simple lookup of the Order Number to return the Customer Name using this formula:
To break down the formula, 1234356 is the search_value or order number, D2:D14 is the lookup_range, and A2:A14 is the result_range. As you can see, Order Number 123456 belongs to Marge Simpson.
Because XLOOKUP can work from left to right as well as right to left, we can do the reverse. Here, we’ll look up Marge Simpson in the range A2 through A14 to find her Order Number in the range D2 through D14.
Missing Value
In this next example, we’ll include “ZERO” for the missing_value. So, if our search_value isn’t found, we’ll see ZERO instead of the default #N/A.
Because our lookup of Homer Simpson isn’t found in the range A2 through A14, our result is ZERO.
Match Mode
For an example using the match_mode argument, we’ll use a search_value of 29 for the Amount in the range F2 through F14 to find the Customer Name in the range A2 through A14.
We’ll include a match_mode of 1 for an exact match or the next greater value. Note that there’s no missing_value argument in the formula.
You can see the result is Raj Koothrappali. Because there’s no match for 29, the formula gives us a result for the next higher value which is 30.
Search Mode
Here’s one more example using both the match_mode and search_mode arguments with the same search_value of 29 in F2 through F14. Again, we look for the Customer Name in the range A2 through A14.
We’ll look for an exact match or the next lower value by searching from the last entry to the first. So, we enter -1 for the match_mode and -1 for the search_mode. Like above, the missing_value is omitted.
As you can see, the result is Michael Kelso. Because there’s no match for 29, the formula gives us the next lower value which is 28. Even though Eric Forman also matches with 28, we performed the search from the last entry to the first (bottom to top), so Michael Kelso is the first result found.
If we were to search from the first entry to the last (top to bottom) using a search_mode of 1 instead of -1, then Eric Forman would be the result found.
When you have a spreadsheet full of data, looking up a value to find its matching data can take time. But if you use XLOOKUP in Google Sheets, you’ll find what you need in a snap.
For more, check out these basic Google Sheets functions you might want to try.
