The XLOOKUP Function is a more complete successor to other lookup functions such as the VLOOKUP, HLOOKUP and LOOKUP Functions. The XLOOKUP Function made its debut in the Microsoft Excel 2021 version, and as such, you wouldn’t find it in previous versions of excel. If you are using Microsoft Excel 2021 version or newer, you don’t have a single thing to worry about. It is also available with your Microsoft Excel 365 subscription.
The XLOOKUP Function is a versatile lookup function that can search for values in both vertical and horizontal ranges, do approximate or exact matches, and use wildcards for partial matches. In today’s post, we would take you through the XLOOKUP Function and all you need to know about it. Let’s begin with the Syntax
Syntax & Arguments
The syntax for the XLOOKUP function is
XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_array: Value to lookup
- lookup array: Range containing values to match with the lookup value in the argument above to determine the row or column number
- return array: This is the range containing the value you want to return
- [if_not_found] – (optional): The value to return if no match is found
- [match_mode] – (optional): With this, you can specify the type of match you want. 0 is the default and it is for exact matches. -1 is for exact match or next smallest, 1 is for exact match or next largest whilst 2 is for wildcard matches.
- [Search mode] – (optional): 1 is to search from the top, -1 is to search from the bottom, 2 is for binary search in ascending order and -2 is for binary search in descending order.
Things to note
- XLOOKUP can lookup data to the left or right of lookup value correspondence in the table array unlike the VLOOKUP Function.
- The XLOOKUP Function allows you specify the message you want to display if it can’t find a match. The default is the #N/A error, but with the [if_not_found] argument, you can specify what to display.
- XLOOKUP performs an exact match by default, but you can change it with the numbers stated in the [match mode] argument above.
- XLOOKUP can work with both vertical and horizontal arrays.
- The lookup and return array dimensions must be similar unless a #VALUE error would be returned.
- Similar to the INDEX function, XLOOKUP returns a reference as a result.
Let’s take a look at some examples to better explain the arguments above.
XLOOKUP Function Examples
As already established, the XLOOKUP is a very versatile lookup function and as such, we would be reviewing various examples to showcase the versatility of this function.
Basic Exact Match
When all the optional arguments are omitted, the XLOOKUP function would return an exact match by default. In our example, we have information on the top 5 albums by sales volume. We only need to lookup claimed sales from our data set using the XLOOKUP function. To do this, we simply use the formula below:
Two-Way Lookup
The illustration in this section is suitable for looking up values on both the vertical and horizontal range within the data set in order to arrive at an exact value. Using the same data set in the previous section, let’s assume that we need to return the album name using the Artist name only. We use the formula below to achieve this:
The first portion of the the Xlookup formula (XLOOKUP(I6,C3:F3) is for identifying the applicable column for our criteria which is “Album” whilst the remaining portion of the formula is to derive the row number containing the Artist’s name (Whitney Houston)
Basic Approximate Match
Using XLOOKUP with approximate match is more suited for returning a value based on the closest match to your lookup value if your lookup Value does not exist in the original data set. A very good scenario for using this would be for the grading of student’s papers. The examiner can have a general range of scores and their grades, and after marking student’s scripts and poupulating scores for each student, the examiner can use the XLOOKUP Approximate match to return each student’s grade instantly.
In our example, we need to return the grade of a score that is not in our original data set, but lies somewhere between. To return the appropriate score, we would make use of the XLOOKUP Function with approximate match.
Return Multiple Values
With the XLOOKUP Function, you can return a range of values instead of just a single cell reference. in the data for this section, we have information about some employees in a company, and we need to return three matching values with a single XLOOKUP Formula
Complex Criteria
In this section, we would be looking at a more complex use of the XLOOKUP function. Our goal is to return multiple values based on multiple criteria. Let’s tweak the previous example a bit. So we have added some other employees to our previous list and taken out some. We need to return multiple values just as we did in the previous example, however we have 3 criteria we must test. The first is for the staff ID to start with 4, second criteria is for the department to be in Finance, and the last criteria is for the Joined month to not be Jan. To return the first match of this in our data table, we use the formula below:
Explaining the Formula:
“=XLOOKUP(1,“: Indicates that this XLOOKUP function would be used to test for multiple criteria
“(LEFT(B6:B13)=4)“: This represents our first criteria range and criteria pair. We are instructing excel to check the first character of each cell in the range B6:B13, and then match it to “4”.
“(C6:C13=”Finance”)“: Our second criteria range and criteria pair. Excel checks whether the values in Cells C6:C13 equals “Finance”.
“NOT(MONTH(D6:D13)=1)“: Third Criteria range and criteria pair. The instruction is to only select cells in D6:D13 that do not have a joined date in Jan of whichever year.
“,B6:E13)“: Return array, excel returns the first-row record within the range that meets all the above criteria.
