Does Google Sheets have Xlookup function?

The XLOOKUP function returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.

Sample Usage

XLOOKUP("Apple", A2:A, E2:E) to replace VLOOKUP("Apple", A2:E, 5, FALSE)

XLOOKUP("Price", A1:E1, A6:E6) to replace HLOOKUP("Price", A1:E6, 6, FALSE)

XLOOKUP where match column is to the right of the output column

XLOOKUP("Apple", E2:E7, A2:A7). The VLOOKUP equivalent is VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE)

Syntax

XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

  • search_key: The value to search for. For example, 42, "Cats", or B24.
  • lookup_range: The range to consider for the search. This range must be a singular row or column.
  • result_range: The range to consider for the result. This range's row or column size should be the same as the lookup_range, depending on how the lookup is done.
  • missing_value: [OPTIONAL - #N/A by default] The value to return if no match is found.
  • match_mode: [OPTIONAL - 0 by default] The manner in which to find a match for the search_key.
    • 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 - 1 by default] The manner in which to search through the lookup_range.
    • 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.

Notes

  • If result_range is more than one row or column, then the output will be the entire row/column at the index a match was found in the lookup_range.

Examples

Lookup table for all examples.

Does Google Sheets have Xlookup function?

XLOOKUP for Total amount sold with match_mode and search_mode omitted and missing argument specified.

XLOOKUP for Total amount sold with match_mode = 0 and search_mode = 1 and -1.

XLOOKUP for Total amount sold with match_mode = 1 and -1 and search_mode omitted.

XLOOKUP using horizontal matching and returning an entire column.

  • XMATCH
  • VLOOKUP
  • HLOOKUP

Was this helpful?

How can we improve it?

The XLOOKUP function in Google Sheets is a new lookup function in Google Sheets that is more powerful and flexible than the older lookup functions like VLOOKUP or HLOOKUP.

XLOOKUP matches a search key in a lookup range and returns the value from a result range at that same position. If XLOOKUP does not find a match, you can specify a default value. You can control the match mode, like other lookup functions, and even control the search mode. More on that below, but first let’s see a simple example.

Here’s a simple XLOOKUP formula that looks for the search key in column A and returns a value from column C:

=XLOOKUP(E2,A2:A11,C2:C11)

It looks like this in the Sheet:

Does Google Sheets have Xlookup function?

🔗 Get this example and others in the template at the bottom of this article.

XLOOKUP Function Syntax

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

It takes a minimum of three and a maximum of six arguments:

search_key

The value you want to search for.

lookup_range

The range to search. It must be either a single column or a single row.

result_range

The range to consider for the result. The return value is taken from the position of the matched value in the lookup array if the search key is found. The result range must match the dimensions of the lookup range.

[missing_value]

The fallback value to return if no match exists. This is an optional argument and if it is omitted, an error is returned if no match exists.

[match_mode]

This optional argument lets you specify what match mode to use. If unspecified, an exact match is used.

The options are:

Option Match Mode Behavior
0 Exact match search
1 Exact match or next value that is bigger than the search key
-1 Exact match or next value that is lower than the search key
2 Wildcard match

[search_mode]

This optional argument lets you specify what search mode to use. If it is omitted, XLOOKUP defaults to searching the lookup range from the first entry to the last entry.

The different search options are:

Option Search Mode Behavior
1 Search from the first entry to the last one
-1 Search from the last entry to the first
2 Search through the range using binary search and assuming the range is sorted in ascending order
-2 Search through the range using binary search and assuming the range is sorted in descending order

XLOOKUP Function Notes

  • The lookup range can only be either a single row or a single column. It cannot be an array with multiple rows and columns.
  • The result range must be compatible with the size of the lookup range. For example, if the lookup range is a column of data with 10 rows and 1 column, then the result range must also have 10 rows (though it can have more than 1 column).

XLOOKUP Function Examples

Let’s see some more examples of the XLOOKUP function in Google Sheets.

Example 1: Basic Exact Match

If you omit the optional match mode argument, the XLOOKUP function will perform an exact match.

I.e. when you write it with only the first three arguments, a search key, a lookup range, and a result range, then it will look for an exact match. We saw this in the example at the top of this page:

=XLOOKUP(E2,A2:A11,C2:C11)

Which works like this in the Sheet:

Does Google Sheets have Xlookup function?

Example 2: Missing Value

Now, we can specify a fallback value if no match is found. This is done with the fourth (optional) argument, e.g.

=XLOOKUP(E2,A2:A11,C2:C11,"No match")

In our Sheet:

Does Google Sheets have Xlookup function?

In this case, the search key “XYZ123” is not found in the lookup array (column A) so the XLOOKUP function returns the fallback missing value, which we set to “No match”.

Example 3: XLOOKUP Function Left

Another benefit with the XLOOKUP function is that the lookup range does not have to be to the left of the result range, which is the case with the VLOOKUP (though there is a complicated workaround with array literals).

The formula does not change, but this time the result range is positioned to the left of our lookup range:

=XLOOKUP(E2,C2:C11,B2:B11,"No match")

As you can see, it works equally well in our Sheet:

Does Google Sheets have Xlookup function?

Example 4: Approximate Match

The fifth argument of the XLOOKUP function determines the matching mode. If it is omitted or set to 0, then an exact match is performed.

However, there are situations where the approximate matching option works really well.

Consider the case when our search key falls between two values in the lookup range. It’s not an exact match, but we might still want to return a result to say that it’s lower than X, or higher than Y.

For example, consider this bank savings scenario:

Does Google Sheets have Xlookup function?

The XLOOKUP formula for this example is:

=XLOOKUP(B8,B2:B5,C2:C5,,-1)

Notice the -1 as the final argument, which tells the function to look for an exact match and if it doesn’t find one, to return the value that is lower in the array.

In this example, it doesn’t find the $137,832 exactly, so it looks at the lower value in the array, i.e. $100,000. This is in position 3 of the lookup array, so it returns the value from the 3rd position of the results array, i.e. 1.25%.

One final thing to mention with this example, notice how the fourth argument is blank. This is where we can specify a “missing value” for when no match is found. However, it’s not required here because we’re using an approximate match anyway.

Example 5: Wildcard Match

XLOOKUP in Google Sheets supports three wildcards, *, ?, and ~.

The star * matches zero or more characters.

The question mark ? matches exactly one character.

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

Let’s see an example that uses a surname to find the full name:

=XLOOKUP("*"&B15,A2:A11,A2:A11,"No match",2)

And another example that uses a surname to return transaction revenue from that row:

=XLOOKUP("*"&B15,A2:A11,D2:D11,"No match",2)

Both formulas are seen in the following image, with the first one in cell B17 and the second in cell B18:

Does Google Sheets have Xlookup function?

There are two important things to notice with this formula:

1) The search key is “Peterson”, but to use it in the XLOOKUP function, we first add the wildcard star character that matches anything before the “Peterson”:

I.e. *Peterson

Note, if there were multiple “Peterson” in this dataset this could cause an issue. In this case, you might want to try using the QUERY function or the FILTER function to return all the “Peterson” results.

2) The match mode in the fifth argument is set to 2, which indicates that this is a wildcard search.

Example 6: Return Multiple Results

The XLOOKUP function can return multiple results for a single match, not just a single result like a VLOOKUP (although there is a workaround for VLOOKUP to return multiple columns).

XLOOKUP returns multiple results by specifying a result range with multiple columns (or rows if you’re doing a horizontal lookup).

The formula is:

=XLOOKUP(B13,A2:A11,B2:E11)

This gives the result:

Does Google Sheets have Xlookup function?

Example 7: Different Search Mode

The final argument lets you change the search method used. The default is to search from top to bottom of your range, but you can change this to search from the bottom to the top if that makes sense.

The XLOOKUP can also perform super quick binary searches, but this requires your data to be sorted correctly to avoid incorrect results.

XLOOKUP Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

It’s part of the Lookup family of functions in Google Sheets. You can read about it in the Google Documentation.

Does Google Sheets have lookup function?

The Google Sheets LOOKUP function searches through a row or column for a key and returns the value of the cell in a result range located in the corresponding position to the search row or column. Like VLOOKUP and HLOOKUP, LOOKUP allows you to retrieve specific data from your spreadsheet.

Is VLOOKUP and Xlookup the same?

XLOOKUP vs VLOOKUP – Key Differences XLOOKUP searches for data both horizontally and vertically. VLOOKUP searches only vertically. XLOOKUP can refer to the left of the lookup_value. It always looks up the values towards the right of the lookup_value.