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:
🔗 Get this example and others in the template at the bottom of this article.
Learn more about working with Lambda Functions, Named Functions, and X-Functions in the FREE Lambda Functions 10-Day Challenge course
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:
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:
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:
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:
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:
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:
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.
Thanks Ben for this GREAT news.
I will look forward to when these new FUNCTIONS are available to me.. There are plenty of other Excel features and functions I wish we could also get for Google Sheets.
Instead of checking ten times daily for the update (because I would :). I created a Google Sheet/Apps Script trigger to check every so often to let me know when it’s available for me. When the XLOOKUP function returns what is expected, then I get an email from a Trigger event.
https://docs.google.com/spreadsheets/d/1CGpXJ8garqI_VXDJdpUZ63itq2FsKySwFYBxiH-usOE/copy
Haha, nice idea! I like it 😉
Is it possible to combine other functions such as FLATTEN or array literals to search multiple columns with XLOOKUP?
When these functions will be available to all users…?
Xlookup appeared today in my workspace account. Lambda and Named Functions appeared earlier this week. I don’t have any of the Lambda helper functions yet. In my regular Google account, only Lambda is available.
There is a “rapid release” option in Workspace to receive new releases as early as possible. See how to activate it here: https://support.google.com/a/answer/172177?hl=en
The rollout seems to be wonky so far. I *really* want to use it *now*.
If I enter an actual Xlookup formula, I get “Unknown function: ‘XLOOKUP’.”
BUT…if I type in *exactly* =xlookup(
…that’s it, just the left parenthesis…and hit return, I get: “Wrong number of arguments to XLOOKUP. Expected between 3 and 6 arguments, but got 0 arguments.”
So it *does* recognize Xlookup when I do it *wrong*, and it doesn’t recognize Xlookup when I do it right…
Hopefully, they’ll have everything fixed soon. It’s such a good “upgrade” to Sheets. I think these formulas will be really useful.
XLOOKUP appeared for me this morning (then disappeared later on…). One thing I noticed having a quick play with it whilst it was available was that you couldn’t create an ARRAYFORMULA XLOOKUP with a vertical array of search_keys, multiple columns in the result_range, and get it to return multiple columns for each match (i.e. something akin to an SQL JOIN, which is currently possible already with an ARRAYFORMULA VLOOKUP) – it only gives you the results from the first column of the result_range in this case… Maybe this is how it behaves in Excel (IDK), or maybe I’m just missing something obvious?
Same issue for me. Not sure if this is expected but it seems to be a big limitation of XLOOKUP.
I think this is what you’re talking about…I managed to get it to work.
If you have e.g.:
=arrayformula(vlookup(A:A,A:D,{2,3,4},false))
You can change it to xlookup using the byrow lambda function:
=byrow(A:A,lambda(row,xlookup(row,A:A,B:D,”error”,false)
I think there is a serious limitation in the XLOOKUP.
It can not give 2 dimensional array output.
In VLOOKUP it is easily possible but in XLOOKUP I noted multi column output is possible for single row. Not for multiple rows.
Whoa, it’s finally arrived!!! HUGE news for us spreadsheet junkies!!
Woohooo!
Does the QR Code have any restricted number can be used? Let say i created a QR Code with a link inside, will it be only can be scan up to 50 times before it no longer can be use?
Saw your great courses – and especially the sheets essentials.
In the course outline I only noticed the Vlookup.
Any plans on having the Xlookup in the course material?
Thanks! Have a look at the new (free) lambda functions course, which includes xlookup: https://courses.benlcollins.com/p/lambdafunctions
Xlookup is great. It has completely wiped out my use of vlookup, and almost all of my use of index/match (unless I need a 2d search).
The “did not find” addition also makes the formula so much cleaner than the old method of wrapping iferror() around lookups.
Are there known limits to XLOOKUP in Sheets? I nailed down an error in my solutions when trying to use columns of ~40K rows as key/value arguments in XLOOKUP. Has anyone encountered something on this issue?
Is there a solution or addition to the formula where if in your example above, there were two or three instances of a result?
eg. you had $6,800 once, but what if it appears multiple times?
Hey guys, you can use XLOOKUP formula with ARRAYFORMULA as below.
It works like a charm!
=IFERROR( ARRAYFORMULA( XLOOKUP(A2:A;’YourTable’!A2:A;’YourTable’!B2:B)) 😉