As it’s a relatively recent addition to Microsoft Excel, you can’t currently use XLOOKUP in Google Sheets. But, you can use a few other functions or combinations to get similar results in Sheets.
Join us as we take a brief look into how XLOOKUP works in Excel and how you can get around missing the function in Google Sheets.
What Is XLOOKUP?
The XLOOKUP function is one of the newer functions introduced fairly recently in Excel in 2019. It works similarly to lookup functions such as HLOOKUP, VLOOKUP, and INDEX with MATCH. However, XLOOKUP is arguably easier to use and more robust.
For people using Microsoft Excel, XLOOKUP has quickly become the preferred method for users performing a search.
Here is the syntax for the XLOOKUP formula:
=XLOOKUP(lookup-value, lookup-array, return-array, [if-not-found], [match-mode], [search-mode])
Here are the parameters used in the formula:
- lookup-value: this is the value you are looking for
- lookup-array: this parameter is the array or cell range you wish to search
- return-array: this is the range or the array to return the value from
- if-not-found: this optional parameter specifies what should be output when a match is not found when the search ends. If left blank, this will return a #N/A error.
- match-mode: this optional parameter controls how matches will be found.
- search-mode: this is also an optional parameter that controls how the formula will perform the search.
Although you can’t use this formula in Google Sheets, knowing the syntax and the parameters used in the formula will be highly beneficial when we look at the alternatives you can use instead of the XLOOKUP formula.
Does Sheets Have XLOOKUP?
Currently, Google Sheets doesn’t have an equivalent XLOOKUP function. This is something that Google might add in a future update. However, this doesn’t mean that you won’t be able to get the same results on your Google Sheets spreadsheet.
Although this might be a reason to choose Excel over Google Sheets, other functions fill a similar purpose in Sheets.
Let’s look at some functions that can be used as an alternative to XLOOKUP in Sheets.
Alternatives for XLOOKUP in Google Sheets
Here’s how to do an XLOOKUP equivalent search in Google Sheets.
The FILTER Function
Using FILTER in Sheets is the best way to get similar results to XLOOKUP in Sheets. Before we demonstrate it with an example, let’s look at the syntax. We changed it to represent something similar to XLOOKUP. It is:
=FILTER(return-array, lookup-array=lookup-value)
We are going to need three parameters here. These are:
- return-array: this parameter defines the range or array to return
- lookup-array: this parameter specifies the array or range you wish to search
- lookup-value: this parameter will define the value or the cell address of the value you’re searching for.
Here is an example of using the FILTER formula as a Google Sheets XLOOKUP equivalent:
The exact formula we used here is:
=FILTER(B2:B7, A2:A7=D2)
The first parameter defines the cell range we want the value from. The second and third parameters define the cell range you want to search from and the value you wish to search, respectively.
The FILTER function is extremely powerful as it can return an array if there is more than 1 matching value. It can also filter depending on conditional criteria and add a condition on more than 1 variable.
The INDEX and MATCH Function
Another XLOOKUP equivalent in Google Sheets is a combination of the INDEX and the MATCH functions together. Let’s take a look at the syntax we will be using for this purpose:
=INDEX(return-range, MATCH(lookup-value, lookup-range, 0))
We are nesting the MATCH formula inside the INDEX function in this example.
Here are the parameters used in these formulas:
- return-range: this parameter is the cell range that contains the value you want to get.
- lookup-value: this parameter defines the value you’re looking for.
- lookup-range:Â this parameter defines the range containing the value in the lookup-value parameter.
Let’s look at what the function looks like in action. We are using the same data from above.
The exact formula we used here is:
=INDEX(B2:B7,MATCH(D2,A2:A7,0))
The first parameter is the range we wish to return the value from. The second parameter is the MATCH function, and this formula has three parameters. The first argument is the value we want to look for in the range defined in the second parameter in the MATCH formula.
HLOOKUP and VLOOKUP
If you’re looking for how to do XLOOKUP in Google Sheets, these functions work almost identically, except you need to know if you’re searching vertically or horizontally across the table. I.e., H for horizontal and V for vertical.
We have complete guides for each of these functions. You can check them out here:
- VLOOKUP Multiple Criteria in Google Sheets
- VLOOKUP From Another Sheet
- How to Use The Google Sheets HLOOKUP Function
XLOOKUP in Google Sheets FAQ
Does Google Sheets Have XLOOKUP?
There is no XLOOKUP on Google Sheets. However, there are alternative functions you can use. These include FILTER, HLOOKUP, VLOOKUP, and INDEX(MATCH)
What Is the Equivalent of XLOOKUP Function in Google Sheets?
Although there are several functions you can use as an alternative to XLOOKUP, mostly FILTER and INDEX(MATCH) will get the job done.
Can You Use XLOOKUP Instead of VLOOKUP?
You can use XLOOKUP instead of VLOOKUP in Exel, and the only difference is that XLOOKUP will work no matter the direction of the lookup, and the returned values will reside in the array or cell range. Unfortunately, XLOOKUP does not work in Google Sheets.
Wrapping Up the Google Sheets XLOOKUP Guide
Although you may run into trouble importing ready-made spreadsheets from Excel containing XLOOKUP formulas, there are other options to use in Google Sheets. We’d recommend FILTER as the closest match, so familiarize yourself with that one first.
Hopefully, in the near future, we’ll be able to use XLOOKUP in Google Sheets.
Related Reading:
