How to use XLOOKUP in Google Sheets

The XLOOKUP function in Google Sheets gives you an easy way to find the data you’re looking for quickly. XLOOKUP does not have the same limitations as VLOOKUP and HLOOKUP, allowing you to search in any direction.

If you are a Microsoft Excel user, you may have used XLOOKUP there. Fortunately, it works the same way in Google Sheets. Whether you’re used to the function in Excel or completely new to it, 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 its accompanying formula, you can perform a search on a range of cells and return one matching result from another. This is useful for sheets containing a large amount of data where using eyeballs is time consuming.

The syntax of the function is XLOOKUP(search_value, lookup_range, result_range, missing_value, match_mode, search_mode).The first three arguments are required. The remaining three arguments can be used to customize your search.

• Lookup_value: The value to search for which can be a number, text, or cell reference. The text must be enclosed in quotation marks.
• search range: The range of cells to search for search_value which must be a single row or column.
• Result_range: The range of cells to search for the result that corresponds to the search_value which must be the same size as the search range.
• Missing value: The value to return if there is no match to the search_value. The formula returns the #N/A error by default.
• Match_mode: How to find the match 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 the next value less than the search_value, or 2 for a wildcard match. The default value is 0.
• Search mode: How to find 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 predetermined value is 1.

How to use XLOOKUP in Google Sheets

To show how the function works, we’ll start with a simple search 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 customer order sheet that includes contact details and order information. For the first example, we’ll do a simple Order Number lookup to return the Customer Name using this formula:

=XLOOKUP(123456,D2:D14,A2:A14)

To break down the formula, 1234356 is the search_value or order number, D2: D14 is the lookup_rangeand 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 and from right to left, we can do the opposite. Here, we will search for Marge Simpson in the range A2 to A14 to find her order number in the range D2 to D14.

=XLOOKUP("Marge Simpson",A2:A14,D2:D14)

Note: Unlike VLOOKUP which works vertically and HLOOKUP which works horizontally, XLOOKUP works in both directions.

missing value

In the following example, we will include “ZERO” for the missing_value. So if our lookup value isn’t found, we’ll see ZERO instead of the default #N/A.

=XLOOKUP("Homer Simpson",A2:A14,D2:D14,"ZERO")

Since our search for Homer Simpson is not in the range A2 to A14, our result is ZERO.

match mode

For an example using the match_mode argument, we will use a search_value from 29 for Quantity in the range F2 to F14 to find the Customer Name in the range A2 to A14.

We will include a match_mode of 1 for an exact match or the next higher value. Note that there is no missing_value argument in the formula.

=XLOOKUP(29,F2:F14,A2:A14,,1)

You can see that the result is Raj Koothrappali. Since there is no match for 29, the formula gives us a result for the next highest value, which is 30.

search mode

Here is one more example using both the match_mode Y search_mode arguments with the same search_value from 29 in F2 to F14. Again, we look for the customer name in the range A2 to A14.

We will search for an exact match or the next lower value by searching from the last input to the first. So, we enter -1 for the match_mode and -1 for the search_mode. As above, the missing_value omitted.

=XLOOKUP(29,F2:F14,A2:A14,,-1,-1)

As you can see, the result is Michael Kelso. Since there is no match for 29, the formula gives us the next lowest value, which is 28. Although Eric Forman also matches 28, we search from the latest entrance to the first (from bottom to top), so Michael Kelso is the first result found.

If we were to search from the first entrance to the latest (from 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 no time.

To learn more, check out these basic Google Sheets features you might want to try.