Introduction

The XLOOKUP Function is a superb lookup function that was introduced by Microsoft, in later versions of Office.

You can use the XLOOKUP Function to find a value or a set of values in an array or dataset. If you are a demand planner or supply planner still using VLOOKUP, you should consider using this function instead. Since it will greatly enhance the functionality of your spreadsheets.

In this tutorial, I am going to go over how to use the XLOOKUP Function for some of the common scenarios you may be faced with.

So, let’s get started.

The XLOOKUP Function – The Syntax Explained

First, let’s get a basic understanding of the syntax of the XLOOKUP Function:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The first three parameters are required and the last three are optional.

lookup_value is simply the value that you want the function to search for.

lookup_array is the range or array where your lookup_value is located.

return_array is the array or range which contains the corresponding value, which the function should return.

[if_not_found] is basically a form of built-in error handling, which tells the function the user-friendly text to display if the lookup_value is not found. If you don’t remember to specify this text, then the #N/A error is returned, if the lookup_value is not found.

[match_mode] tells the function what type of match is needed. If you use 0 which is the default, then it’s an exact match. -1 indicates that if an exact match is not found, the next smaller value must be returned. 1 indicates that if an exact match is not found, then the next larger value must be returned. 2 indicates that a wildcard character match must be used since either the *, ? or ~ character has been incorporated.

[search_mode] tells the function which search mode it must use. 1 indicates that the search must start from the first item. This is the default. -1 indicates that the search must start from the last item. 2 indicates that it must be a binary search, with the lookup_array being sorted in ascending order. -2 indicates that it must be a binary search, with the lookup_array being sorted in descending order.

The Main Differences Between XLOOKUP and VLOOKUP

Let’s review the main differences between XLOOKUP and VLOOKUP.

Simple Example Using XLOOKUP for An Exact Match

EBITDA stands for Earnings Before Interest, Taxes, Depreciation and Amortization. It’s considered to be an indicator of a company’s profitability.

While I always caution my clients that this isn’t a metric recognized by Generally Accepted Accounting Principles (GAAP), it can give one a quick snapshot of a company’s financial health. You can also use it in a comparative analysis involving two or more companies.

The formula used to calculate EBITDA is given below.

So, let’s start by looking at an example that demonstrates how to calculate EBITDA in Excel.

In the dataset below, we have some key financial information from a hypothetical business. This information was drawn from their annual report.

We want to calculate the EBITDA of Company XYZ:

• In cell B8, enter the following formula.

=SUM(B3:B6)

• The EBITDA of Company XYZ is $26,000,000.

In our next example, we have a list of companies in column A and their respective EBITDA’s given in column B.

In cell D3, we have the name of one of the companies given. We want to use the XLOOKUP Function to return the corresponding EBITDA of Company QMM, in cell E3:

• In cell E3 enter the following formula.

=XLOOKUP(D3,A4:A17,B4:B17)

 

 

Short Explanation:

Since this is a very simple exact match – we don’t need to specify any of the optional parameters.

The lookup_value is given in cell D3. This is the company name – Company QMM in this case, which the XLOOKUP Function will try and find in the lookup_array.

The lookup_array is the range A4:A17, which contains all the company names. The return_array is range B4:B17 which contains all the respective EBITDA values.

So, the function searches for Company QMM in the lookup_array and once it finds this value, it looks for the corresponding match in the return_array which is $21,000,000. This is the value returned in cell E3.

More Complex Example Using XLOOKUP for An Approximate Match

It’s recommended that buyers order stock in bulk while still considering the demand of the product. Since suppliers will often provide different discount rates depending on the amount of stock ordered.

It’s an important part of supplier analysis and optimization of processes, to evaluate and take advantage of discount rates.

In the dataset below, we are shown the amount in units, of Product A that needs to be ordered for each discount rate.

We can see that 15237 units of Product A has been ordered and we want the appropriate discount rate returned in cell E3:

• In cell E3 enter the following formula.

=XLOOKUP(D3,A4:A6,B4:B6,,-1,)

 

Short Explanation:

In this example, we are specifying an optional parameter in addition to the required parameters. We are specifying that the match_mode is -1.

This means that if an exact match isn’t found then the function must search for the next smaller value and return the corresponding value from the return_array. This is referred to as an approximate match.

The lookup_value in this case is the value in cell D3 which is 15237. This is the amount of Product A that has been ordered. The lookup_array is range A4:A6. The return_array is range B4:B6.

So, the function looks for 15237 in range A4:A6 and doesn’t find an exact match. However, since we specified this is an approximate match and that the function should find the first value smaller than 15237 in the table, the function finds 10000. It then returns the corresponding discount rate from column B, which is 2%.

If you’d like to learn more about the role of analytics in supply chain planning then read this post.

Using XLOOKUP to Return Multiple Values

Supply Chain Finance refers to a set of digital solutions that assists with facilitating cash flow between a buyer and a supplier. If a supplier requires immediate payment rather than the standard 30-day invoice period, they can enter into an agreement with the buyer.

In this kind of transaction, the buyer approves the early payment, and the payment is made by a bank or another financial institution the buyer is affiliated with.

The financial institution or provider issues the payment to the supplier. The buyer, in return, receives extended payment terms and then pays the financial institution back. This arrangement works best when the buyer has a better credit rating than the supplier.

In the dataset below, we have a list of suppliers in column A. The product they sell is given in column B. Column C records whether the supplier at hand has requested supply chain finance. Column D lists whether the supplier has a better or worse credit rating than the buyer.

We have the name of the supplier entered in cell A13, and we want all the corresponding matches returned:

• In cell B13, enter the following formula.

=XLOOKUP(A13,A4:A9,B4:D9)

 

Short Explanation:

In this example, the lookup_value is the value in cell A13, which is Supplier DDX. The lookup_array is range A4:A9. However, since we want to return more than one value, this time we don’t just specify one column as the return_array. Instead, we specify three columns, given by range B4:D9, as the return_array.

So, the function looks for Supplier DDX in range A4:A9 and once it finds this value, it looks for all the corresponding values, in column B, column C and column D. It returns these values, and they spill over into the other cells. This is referred to as the spill range.

Using XLOOKUP With a Wildcard

Consistent engagement with the sales team is a vital part of a demand planner’s job. The sales team will usually have an in-depth understanding of market trends, the sales funnel and client pain points and preferences. This knowledge will assist the demand planner with forecasting and developing models that are accurate.

In our example, we have a hypothetical major lifestyle brand that sells energy drinks. Our demand planning team wants to evaluate the effect of naming conventions on sales.

They basically want to see if all other variables are equal (for example all the drinks have the same composition and the same amount spent on marketing), what effect incorporating certain keywords into the name has on sales.

The source dataset is shown below. The names of the energy drinks are given in column A. Each of the drinks incorporates the term Lotsa, the primary keyword and 4 U in their names. We also have the corresponding sales shown in column B.

We have the keyword, which is part of the name, given in cell D4. We want to return the corresponding sales, for the energy drink which has Strength incorporated in the name, in cell E4:

• In cell E4, enter the following formula.

=XLOOKUP(“*”&D4&”*”,$A$4:$A$8,$B$4:$B$8,,2,)

• Drag the formula down the column to see the following.

 

Short Explanation:

In this example, we needed to specify one of the optional parameters since we are dealing with a wildcard search. Thus, we specify that our match_mode is 2.

The lookup_value in this case is a combination of wildcard characters and the value in cell D4. So as a result, the function searches for the word strength, no matter how many characters precede or follow this specific word. Once it finds a match, it returns the corresponding sales value.

You can read more about how to outsmart supply chain vulnerabilities in this post.

Error Handling with XLOOKUP

When it comes to inventory management, there are two main things demand planners usually have to consider. This relates to the amount of stock the company has in storage, at any point in time.

It can be beneficial to have additional stock since having inventory ahead of an order prevents the costs involved, in having to obtain it on the last minute. On the other hand, there is a disadvantage in having too much stock in storage, since storage in terms of space, and other related factors – is an additional expense.

So, demand planners have to consider this and pay close attention to inventory management in their forecasting models.

So, let’s look at an example with XLOOKUP involving inventory management and error handling.

In the dataset below we have a list of Products Codes given in column A. The amount of the respective product currently stored in the inventory, is given in column B.

We have entered a Product Code in cell A13 that isn’t in the list, and we want XLOOKUP to return a user-friendly, informative error message instead of #N/A:

• In cell B13 enter the following formula.

=XLOOKUP(A13,A4:A10,B4:B10,”This Product Code is Not in the List”,,)

 

Short Explanation:

In this example, we are specifying an optional parameter. We are specifying in quotation marks the if_not_found parameter.

This means that if the function doesn’t find the lookup_value in the lookup_array then it should return this text in the cell. Since the function didn’t find Product Code SKU-777-AAA, in the lookup_array the explanative text “This Product Code is Not in the List” is returned in cell B13.

Conclusion

In this tutorial, I have given you a comprehensive guide to using the XLOOKUP Function in Excel. It’s easy to understand and instead of using multiple functions in conjunction with VLOOKUP, you can now use XLOOKUP instead.

I hope you found this tutorial useful. Please let us know in the comments below, about your thoughts on the new XLOOKUP Function.

A special thank you to Taryn Nefdt for collaborating on this article.