An advanced LOOKUP function. Plus an alternative for Google Sheets.
Excel released a powerful LOOKUP function called XLOOKUP in 2019. It can perform a lookup both vertically and horizontally, hence, is meant as a replacement for Excel’s existing LOOKUP functions: VLOOKUP, HLOOKUP, INDEX/MATCH.
In this article, we will go over everything you need to know about the XLOOKUP function, along with some examples.
- What is XLOOKUP function?
- How to get access to XLOOKUP?
- Syntax for XLOOKUP function
- How to use the XLOOKUP function
- Example 1: XLOOKUP to fetch a lookup value vertically
- Example 2: XLOOKUP to fetch multiple values
- Example 3: XLOOKUP with multiple criteria
- Example 4: XLOOKUP to fetch a lookup value horizontally
- How to use LOOKUP with Airboxr?
What is XLOOKUP function?
XLOOKUP function in Microsoft Excel allows you to search for an item in a vertical or horizontal range and return its corresponding value. Unlike VLOOKUP/HLOOKUP, XLOOKUP can lookup data in any direction of lookup values.
XLOOKUP can lookup data in any direction of lookup values.
How to get access to XLOOKUP?
XLOOKUP is only available for the users of Microsoft 365. If you are using Microsoft 365 suite on your computer, then this function will be automatically enabled on your Excel.
If you’re on older Microsoft 365 edition, such as home, personal or university and can’t access XLOOKUP function, then you can follow these steps:
- Go to File → Account
- Join the Officer Inside Program
If you’re not on Microsoft 365, then you might need to upgrade your version to access XLOOKUP.
You can also access XLOOKUP from online Excel. To do so, you would first have to sign into OneDrive on the web. Then either create a new excel sheet or upload your existing excel file in your OneDrive folder.
Syntax for XLOOKUP function
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP function takes up to six arguments.
- lookup_value (required): the value to search for
- lookup_array (required): the array or range to search for the lookup value.
- return_array (required): the array or range to return your matching value from
- [if_not_found] (optional): return this value when the match isn’t found
- [match_mode] (optional): specify your match type
- [search_mode] (optional): specify the search mode
Values you can use for [match_mode]:
- 0 (default): Exact match. If not found, return #NA.
- 1: Exact match. If not found, return the next larger value.
- 2: A wildcard match where *, ?, and ~ have special meaning.
Values you can use for [search_mode]:
- 1 (default): Perform a search starting at the first item.
- -1: Perform a reverse search starting at the last item.
- 2: Perform a binary search where the data needs to be sorted in ascending order. If not sorted, return invalid.
- -2: Perform a binary search where the data needs to be sorted in descending order. If not sorted, return invalid.
How to use the XLOOKUP function
Let’s take a look at a few examples to get a better understanding of how to use XLOOKUP.
Example 1: XLOOKUP to fetch a lookup value vertically
For the purpose of this blog, we’ve created a table containing customer data of a store as shown below. Let’s assume we want to find the total amount spent by the customer Christian.
Let's look at the format we want the output to be in. We have the customer’s name with the amount spent in a new table shown below:
We want to display the amount spent by Christian in cell L2. Now let’s go to L2 and type in the following formula.
=XLOOKUP(K2, B1:B10, E1:E10)
=XLOOKUP("Christian", B1:B10, E1:E10)
Here, K2 or "Christian" is the lookup_value, B1:B10 is the lookup_array and E1:E10 is the return_array. You can use either a cell reference or a string as a lookup value. If you’re using a string, it should be enclosed in quotes.
Now, let’s see what happens when return_array is on the left of lookup_array.
Let’s take the same dataset but with the first name column on the right.
Here we're trying to perform a lookup to the left of the lookup value. Let’s use the following formula.
=XLOOKUP("Christian", E1:E10, D1:D10)
We will still get the same result.
XLOOKUP addresses one of the major issues with the VLOOKUP function. You can move the rows and columns around, and XLOOKUP would still work. VLOOKUP would have thrown an error.
Example 2: XLOOKUP to fetch multiple values
With XLOOKUP, we can also fetch multiple values. Suppose we want to get the values of Christian’s country name and the amount spent. We can use the following formula.
=XLOOKUP("Christian", B1:B10, D1:E10)
Here, the formula uses a return_array that contains a range of two columns (D1:E10). Therefore, when the lookup value is found in the lookup_array (B1:B10), the formula returns the values of the entire row from the return_array.
Example 3: XLOOKUP with multiple criteria
XLOOKUP also allows you to perform lookups when you have more than 1 criterion. Let’s take the same dataset as above. Now assume you want to find out the amount spent by Christian Paul. Our dataset has separate columns for first and last names. So to perform this query, you would have to look up both the values Christian and Paul in the dataset. With XLOOKUP, this can be done easily.
We can simply type the following formula in the required cell.
=XLOOKUP("Christian" & "Paul", B1:B10 & C1:C10, D1:E10)
Here, the formula uses two lookup values ("Christian" and "Paul") and two lookup arrays ( B1:B10 & C1:C10). It is important to note that when using multiple criteria, you need to add the required lookup values and lookup arrays using an ampersand (&).
Example 4: XLOOKUP to fetch a lookup value horizontally
Now let’s take a look at how XLOOKUP can be used to look up horizontally. For this example, we have transposed the above dataset. So the new dataset looks like this.
Let’s take the same case as shown in Example 1. We want to find the total amount spent by the customer Christian. We can use this formula.
=XLOOKUP("Christian", A2:J2, A5:J5)
Here, instead of a vertical range, we have a horizontal range for both lookup_array and return_array.
XLOOKUP is a powerful LOOKUP function that can help you search through your Excel spreadsheets in an instant. It does not exist in Google Sheets yet. So if you’re someone who prefers working with Google Sheets over Excel, you’d still have to work with older LOOKUP functions, or better yet use Airboxr.
You can use Airboxr to lookup data in any direction, just like XLOOKUP in Excel. And it works in Google Sheets.
How to use LOOKUP with Airboxr?
Let's see how we can fetch a lookup value with Airboxr. Let’s suppose we have the same dataset as shown above in a Google Sheet. Now go to a new sheet and paste the customer’s first name on column A. Let’s keep First_Name as the title.
Now, we launch Airboxr and start our lookup flow: select the data source which has the customer data you want to look up. If your data source is not present in the list, then paste the GSheet URL of the sheet containing your source data and hit enter. Check out this GIF below.
Now, choose the columns you wish to pull—you can choose as many as you want from the source table, but we will choose Country and Amount Spent for consistency. It does not matter whether the columns are to the right or left of the names we pasted in the previous step.
In the next step, we will match the keys: we tell the add-on which column (First_Name) on the active sheet should match against which column (First Name) on the source data.
That's all! ⚡️
With Airboxr, you need not worry about remembering all the complex formulas. You just need to have your data source on a google sheet and have view access to it.
Try Airboxr now and give it a shot yourself!