Excel’s new XLOOKUP replaces VLOOKUP and provides a powerful replacement for one of Excel’s most popular functions. This new feature solves some of the limitations of VLOOKUP and provides additional functionality. Here’s what you need to know.
What is XLOOKUP?
The new XLOOKUP function provides solutions to some of VLOOKUP’s biggest limitations. In addition, it also replaces HLOOKUP. For example, XLOOKUP can look left, the default exact match, and you can specify a range of cells instead of a column number. VLOOKUP isn’t as easy to use or as versatile. We’ll show you how it all works.
Currently, XLOOKUP is only available to Insiders program users. Anyone can join the Insider program to access new Excel features as they become available. Microsoft will soon begin rolling out to all Office 365 users.
Let’s dive straight into the action with an example of XLOOKUP in action. Take the data from the example below. We want to return column F’s department for each ID in column A.
This is a classic example of an exact match search. The XLOOKUP function requires only three pieces of information.
The image below shows XLOOKUP with six arguments, but only the first three are needed for an exact match. So let’s focus on them:
- lookup value : What are you looking for.
- lookup_array : Where to look.
- return_array : the range that contains the value to return.
The following formula works for this example: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Now let’s look at some advantages of XLOOKUP over VLOOKUP.
No more column index number
VLOOKUP’s infamous third argument was to specify the column number of information to be returned from an array. This is no longer a problem because you can use XLOOKUP to select the return area (column F in this example).
And remember that XLOOKUP can display data to the left of the selected cell while VLOOKUP cannot. More on that below.
You also no longer have the problem of the broken formula when inserting new columns. When this happens in your spreadsheet, the yield range would automatically adjust.
Exact match is the default
When learning VLOOKUP, it was always confusing having to state why you wanted an exact match.
Luckily, XLOOKUP uses an exact match by default – the most common reason for using a search formula). This reduces the need to answer that fifth argument and reduces errors from users new to the formula.
In short, XLOOKUP asks fewer questions than VLOOKUP, is more user-friendly and also more durable.
XLOOKUP can look to the left
The ability to select a search scope makes XLOOKUP more versatile than VLOOKUP. With XLOOKUP, the order of the table columns does not matter.
VLOOKUP was restricted by finding the leftmost column of a table and backtracking a specified number of columns to the right.
In the example below, we need to search for an ID (column E) and return the person’s name (column D).
The following formula achieves this: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
What to do in the event of a burglary
Search function users are well acquainted with the #N/A error message that greets them when their VLOOKUP or MATCH function can’t find what they need. And there is often a logical reason for this.
Hence, users are quick to look for ways to hide this error as it is not accurate or helpful. And of course there are ways to do this.
XLOOKUP has its own built-in “if not found” argument to handle such errors. Let’s see this in action using the previous example, but with an incorrectly entered ID.
The following formula displays the text “Incorrect ID” instead of the error message: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Incorrect ID”)
Using XLOOKUP for a range search
Although not as common as exact match, a very effective use of a search formula is to search for a value within ranges. Consider the following example. We would like to make the discount based on the amount spent.
This time we’re not looking for a specific value. We need to know where the values in column B fall within the ranges in column E. This determines the reduction achieved.
XLOOKUP has an optional fifth argument (remember, it defaults to exact match) called the match mode.
You can see that XLOOKUP has greater abilities with fuzzy matches than VLOOKUP.
It is possible to find the closest match less than (-1) or the closest match greater than (1) the value you are looking for. Wildcard characters (2) such as ? where the *. This setting is not enabled by default like VLOOKUP was.
The formula in this example returns the next value less than what is searched for if an exact match is not found: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
However, there is an error in cell C7, where the #N/A error is returned (the “if not found” argument was not used). This error should have resulted in a 0% discount because 64 issues did not meet the discount criteria.
Another benefit of the XLOOKUP function is that the search scope doesn’t have to be in ascending order like VLOOKUP.
Enter a new row at the end of the lookup table, and then open the formula. Expand the used area by clicking and dragging the corners.
The formula corrects the error immediately. Having the “0” at the bottom of the range is not a problem.
I personally would always order the table by the lookup column. Having a “0” at the bottom would drive me crazy. But the fact that the formula hasn’t been broken is brilliant.
XLOOKUP also replaces the HLOOKUP function
As already mentioned, the XLOOKUP function is also there to replace HLOOKUP. A function that replaces two. Excellent !
The HLOOKUP function is a horizontal search used to search along lines.
Not as popular as its VLOOKUP sibling, but useful for examples like the following, where the headings are in column A and the data is in rows 4 and 5.
XLOOKUP can look both ways – down columns and also along rows. We no longer need two different functions.
This example uses the formula to return the sales value relative to the name in cell A2. It looks for the name in line 4 and returns the value of line 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP can search from bottom to top
In general, it is necessary to traverse a list to find the first (and often only) occurrence of a value. XLOOKUP has a sixth argument called lookup mode. This allows us to change the search mode to start at the bottom and instead search a list to find the last occurrence of a value.
In the example below, we want to know the stock level of each product in column A.
The lookup table is organized by date and there are multiple inventory checks per product. We want to return the inventory since the last check (last occurrence of the product ID).
The sixth argument of the XLOOKUP function offers four options. We are interested in using the “Search from last to first” option.
The finished formula is shown here: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
In this formula, the fourth and fifth arguments are ignored. It’s optional, and we wanted an exact match by default.
Tour d’Horizon
The XLOOKUP function is the long-awaited successor to the VLOOKUP and HLOOKUP functions.
Various examples were used in this article to demonstrate the benefits of XLOOKUP. One of them is that XLOOKUP can be used on sheets, booklets and also with tables. The examples have been simplified in the article to facilitate our understanding.
Due to Excel’s upcoming introduction of dynamic arrays, it can also return a range of values. This is a point that requires further investigation.
VLOOKUP days are numbered. XLOOKUP is here and will soon be the de facto search formula.
https://www.howtogeek.com/439286/how-to-use-the-xlookup-function-in-microsoft-excel/