If you use Excel a lot, you have probably used LOOKUP functions quite often. If you're not familiar, the LOOKUP function is used to search through a single row or column to find a specific value in another row or column.
This function comes in handy when working with a lot of data in an Excel sheet. In Excel, there are several types of LOOKUP functions.
In this article, we will tell you everything you need to know about the XLOOKUP function in Microsoft Excel, along with some helpful examples.
What Is XLOOKUP Function in Excel?
XLOOKUP replaces the old functions like HLOOKUP, VLOOKUP, and LOOKUP. It supports vertical and horizontal lookups.
In other words, the XLOOKUP function allows you to quickly find a value in a given dataset, both horizontally and vertically, and returns its corresponding value in a different row or column.
There are many ways to use the XLOOKUP function to get the most out of it. We'll take a look at some of the XLOOKUP examples that would give you a better understanding of it.
How to Access XLOOKUP Function
Unfortunately, XLOOKUP is not available for users running Office 2010, 2013, 2016, and 2019 versions. It is only available on the Microsoft Office 365 suite.
If you're not using Microsoft 365 suite on your computer, you might need to upgrade to Microsoft 365 to get access to the XLOOKUP function. And if you are already using Microsoft 365, then you will find the option already enabled in Excel. XLOOKUP is also available on Office 365 Online.
Syntax for XLOOKUP Function
XLOOKUP function's syntax is similar to that of VLOOKUP and HLOOKUP. If you have ever used them, you'll find using XLOOKUP more convenient. Here's the syntax for the XLOOKUP function in Excel:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XLOOKUP function supports up to six arguments, and here are their values.
- lookup_value (required): the value that you want to find.
- lookup_array (required): the array where you want to look for the lookup value.
- return_array (required): the array from where you want to fetch and return the values when the lookup value is found.
- [if_not_found] (optional): When the match isn't found, this is the value to be returned.
- [match_mode] (optional): This argument lets you specify the type of match you're looking after. There are different values to specify it:
- 0 - It looks for an exact match, and the value should exactly match the value in the lookup_array. It is also set as default when not mentioned.
- -1 - It looks for the exact match and, once found, returns to the next smaller value.
- 1 - It looks for the exact match and once found returns to the next larger value.
- 2 - It does partial matching using wildcards where *, ?, and ~ have special meaning.
- [search_mode] (optional): Used to specify the XLOOKUP's search mode in lookup_array. There are different values to specify the same:
- 1 - Performs search starting from the first item. It's set as default when nothing is specified.
- -1 - Performs reverse search starting at the last item.
- 2 - Performs a binary search in the lookup_array where the data needs sorting in ascending order. If the data isn't sorted, it may produce errors or wrong results.
- -2 - Performs a binary search in the lookup_array where the data needs sorting in descending order. If the data isn't sorted, it may produce errors or wrong results.
Advantages and Disadvantages of XLOOKUP Function in Excel
XLOOKUP is still a function that has its advantages over VLOOKUP and INDEX/MATCH. But it has its disadvantages too.
Advantages of XLOOKUP Function
- It works both vertically and horizontally.
- Needs three arguments, instead of four arguments in the VLOOKUP and INDEX MATCH functions.
- Always defaults to an exact match.
- Can perform partial match lookups using wildcards.
- Can perform lookups in descending order.
- Uses one function instead of two in INDEX MATCH.
Disadvantages of XLOOKUP Function
- Optional arguments may look complex to beginners.
- Can consume more time when selecting two ranges, and there are too many cells in the spreadsheet.
- Returns an error when lookup and array returns are not of the same length.
- Need to remember both lookup and return ranges.
How to Use the XLOOKUP Function in Excel
The XLOOKUP function works similarly to the LOOKUP function in Excel. You can use XLOOKUP by simply selecting cell references where the function should operate.
Alternatively, you can also use the “Formula bar” box located at the top; and insert the XLOOKUP function syntax there.
Examples of XLOOKUP Function
If this is all confusing to you, we'll try to explain it through the following examples. These examples will give you a better idea on how XLOOKUP function works.
Example 1
Let's assume you have a dataset that looks like this (in the image below), and you want to get the Science marks of Matthew in the F and G cells.
And to get that data, we'll use the following XLOOKUP Function in the F2 cell:
=XLOOKUP(F2,A2:A15,B2:B15)
And the formula would result in the Science score of Matthew in the next cell. This is because we gave the lookup value as F2, which in this case is Matthew.
Example 2
Let's take the same dataset, but with the names on the right side this time. Again, we're still looking for Matthew's Science score.
Here's the XLOOKUP function that we will use in this case:
=XLOOKUP(F2,D2:D15,A2:A15)
The resulting data of this function will be the same as well. Even though it's the same result, the lookup_array and return_array values change here.
The XLOOKUP function addresses the major issues with VLOOKUP function. You can move the rows and columns in any manner, and XLOOKUP would still work. But VLOOKUP would return an error.
Example 3
You can also fetch multiple results horizontally and vertically at the same time. So if we want to get Matthew's score in all subjects, that is also possible. The XLOOKUP function in our case would be:
=XLOOKUP(F2,D2:D15,A2:C15)
The resultant of this function would be the same.
In this case, the XLOOKUP function performed a horizontal and a vertical search, and the results were completely accurate. Here, the argument values were modified accordingly.
Use XLOOKUP Instead of VLOOKUP
XLOOKUP is a great improvement when it comes to lookup functions. The only drawback to XLOOKUP function is that it isn't backward compatible. If you share spreadsheets with older versions of Microsoft Office Suite, you may experience errors.
However, you can access XLOOKUP from Office 365 Online. The XLOOKUP funtion is still in its early stages, so Excel users will take some time to adopt it.