Mastering XLOOKUP: A Step-by-Step Guide to Leveraging Microsoft Excel's Powerful Search Tool

Mastering XLOOKUP: A Step-by-Step Guide to Leveraging Microsoft Excel’s Powerful Search Tool

Quick Links

Excel’s new XLOOKUP will replace VLOOKUP, providing a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and has extra functionality. Here’s what you need to know.

What is XLOOKUP?

The new XLOOKUP function has solutions for some of the biggest limitations of VLOOKUP . Plus, it also replaces HLOOKUP. For example, XLOOKUP can look to its left, defaults to an exact match, and allows you to specify a range of cells instead of a column number. VLOOKUP is not this easy to use or as versatile. We’ll show you how it all works.

For the moment, XLOOKUP is only available to users on the Insiders program. Anyone can join the Insiders program to access the newest Excel features as soon as they become available. Microsoft will soon begin to roll it out to all Office 365 users.

How to Use the XLOOKUP Function

Let’s dive straight in with an example of XLOOKUP in action. Take the example data below. We want to return the department from column F for each ID in column A.

This is a classic exact match lookup example. The XLOOKUP function requires just three pieces of information.

The image below shows XLOOKUP with six arguments, but only the first three are necessary for an exact match. So let’s focus on them:

Lookup_value: What you are looking for.
Lookup_array: Where to look.
Return_array: the range containing the value to return.

The following formula will work for this example:

    `=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)`

Let’s now explore a couple of advantages XLOOKUP has over VLOOKUP here.

No More Column Index Number

The infamous third argument of VLOOKUP was to specify the column number of the information to return from a table array. This is no longer an issue because XLOOKUP enables you to select the range to return from (column F in this example).

And don’t forget, XLOOKUP can view the data left of the selected cell, unlike VLOOKUP. More on this below.

You also no longer have the issue of a broken formula when new columns are inserted. If that happened in your spreadsheet, the return range would adjust automatically.

Exact Match is the Default

It was always confusing when learning VLOOKUP why you had to specify an exact match was wanted.

Fortunately, XLOOKUP defaults to an exact match—the far more common reason to use a lookup formula). This reduces the need to answer that fifth argument and ensures fewer mistakes by users new to the formula.

So in short, XLOOKUP asks fewer questions than VLOOKUP, is more user-friendly, and is also more durable.

XLOOKUP can Look to the Left

Being able to select a lookup range makes XLOOKUP more versatile than VLOOKUP. With XLOOKUP, the order of the table columns does not matter.

VLOOKUP was constrained by searching the left-most column of a table and then returning from a specified number of columns to the right.

In the example below, we need to lookup an ID (column E) and return the person’s name (column D).

The following formula can achieve this:

    `=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)`

What to Do If Not Found

Users of lookup functions are very familiar with the #N/A error message that greets them when their VLOOKUP or their MATCH function cannot find what it needs. And often there is a logical reason for this.

Therefore, users quickly research how to hide this error because it is not correct or useful. And, of course, there are ways to do so.

XLOOKUP comes with its own built-in “if not found” argument to handle such errors. Let’s see it in action with the previous example, but with a mistyped ID.

The following formula will display 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 Lookup

Although not as common as the exact match, a very effective use of a lookup formula is to look for a value in ranges. Take the following example. We want to return the discount dependent upon the amount spent.

This time we are not looking for a specific value. We need to know where the values in column B fall within the ranges in column E. That will determine the discount earned.

XLOOKUP has an optional fifth argument (remember, it defaults to the exact match) named match mode.

You can see that XLOOKUP has greater capabilities with approximate matches than that of VLOOKUP.

There is the option to find the closest match smaller than (-1) or closest greater than (1) the value looked for. There is also an option to use wildcard characters (2) such as the ? or the *. This setting is not on by default like it was with VLOOKUP.

The formula in this example returns the closest less than the value looked for if an exact match is not found:

    `=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)`

However, there is a mistake in cell C7 where the #N/A error is returned (the ‘if not found’ argument was not used). This should have returned a 0% discount because spending 64 does not reach the criteria for any discount.

Another advantage of the XLOOKUP function is that it does not require the lookup range to be in ascending order as VLOOKUP does.

Enter a new row at the bottom of the lookup table and then open up the formula. Expand the used range by clicking and dragging the corners.

The formula immediately corrects the error. It is not a problem with having the “0” at the bottom of the range.

Personally, I would still sort the table by the lookup column. Having “0” at the bottom would drive me crazy. But the fact that the formula didn’t break is brilliant.

XLOOKUP Replaces the HLOOKUP Function Too

As mentioned, the XLOOKUP function is also here to replace HLOOKUP . One function to replace two. Excellent!

The HLOOKUP function is the horizontal lookup, used for searching along rows.

Not as well known as its sibling VLOOKUP, but useful for examples like below where the headers are in column A, and the data is along rows 4 and 5.

XLOOKUP can look in both directions - down columns and also along rows. No longer do we need two different functions.

In this example, the formula is used to return the sales value relating to the name in cell A2. It looks along row 4 to find the name, and returns the value from row 5:

    `=XLOOKUP(A2,B4:E4,B5:E5)`

XLOOKUP Can Look From the Bottom-Up

Typically, you need to hunt down a list to find the first (often only) occurrence of a value. XLOOKUP has a sixth argument named search mode. This enables us to switch the lookup to start at the bottom and look up a list to find the last occurrence of a value instead.

In the example below, we would like to find the stock level for each product in column A.

The lookup table is in date order, and there are multiple stock checks per product. We want to return the stock level from the last time it was checked (last occurrence of the Product ID).

The sixth argument of the XLOOKUP function provides four options. We are interested in using the “Search last-to-first” option.

The completed formula is shown here:

    `=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)`

In this formula, the fourth and fifth argument were ignored. It is optional, and we wanted the default of an exact match.

Round-Up

The XLOOKUP function is the eagerly awaited successor to both the VLOOKUP and HLOOKUP functions.

A variety of examples were used in this article to demonstrate the advantages of XLOOKUP. One of which is that XLOOKUP can be used across sheets, workbooks and also with tables. The examples were kept simple in the article to help our understanding.

Due to dynamic arrays being introduced into Excel soon, it can also return a range of values. This is definitely something worth exploring further.

The days of VLOOKUP are numbered. XLOOKUP is here and will soon be the de facto lookup formula.

Win Able