Excel Formula Challenges: How To Do A Retail Inventory Review With XLOOKUP

I've just taken to Instagram to share my entrepreneur story, what consultant life is like, and to give a behind the scenes look at the Business Partnering Institute. You can go follow me there if that sounds interesting to you by clicking here.

This is the channel "Here's the Future of FP&A and Business Partnering" with more than 11,000 subscribers. You can subscribe too and receive my weekly blog directly in your inbox.

This article is co-written by Serdar Barbaros Ozsoy and Anders Liu-Lindberg

For the past two months, I’ve been covering Excel best practices to increase your productivity together with Jesper Martin Jensen. Now I’ve teamed up with another Excel expert, Serdar Barbaros Ozsoy to tell you more about how to pick the best formulas to solve specific problems.

We start with the classic VLOOKUP formula which most Excel users have a love-hate relationship with. The thing is, there are many different alternatives to VLOOKUP and for years INDEX(MATCH) was widely marketed. However, Microsoft has recently introduced a new super formula that trumps all other alternative solutions.

“XLOOKUP”

This week we’ll show you how to use XLOOKUP to do a retail inventory review which you would’ve typically used VLOOKUP for. Next week we’ll show you all the other 10 alternatives and you can make up your mind which one you think works best.

How to simplify your inventory review with XLOOKUP

Defining the problem: Our case study is you doing some analysis for the supply chain team. You are given a lot of raw data to do an analysis of a cycling company. As the initial task, you are assigned to analyze the inventory data. You have two tables as the starting point.

There’s an inventory table with ProductID, location, shelf, and quantity details and another lookup table with ProductID column and other product details. In the inventory table, you need to add a new name column and lookup the value from the lookup table.

Common practice: The most common practice is to use a VLOOKUP formula.

Using tables, your formula (Formula #1) might look like this:

You will be looking up for ProductID, at the row level, in Lookup_Table and you will be returning the corresponding value in the third column. FALSE means that you are looking for an exact match. When column number is hardcoded like the formula above, your lookup will be inaccurate if you add additional columns between the first column and your lookup column since your formula will not recognize that the column number has changed. It will always be looking at the hardcoded column.

Alternatively, your formula (Formula #2) might look like this:

In this example, the only difference from Formula #1 is that it takes the column number differences between two columns and makes the column index number dynamic.

This is a better formula when compared with Formula #1. However, this formula has another weakness. If you change the column locations and it results in a scenario where ProductID column precedes Name column, this formula will break down.

Use VLOOKUP alternatives: As finance professionals, we tend to have a love relationship with VLOOKUP because it is generally the first formula we learn when we all start our professional lives. However, it makes sense to abandon it considering there are much more functional and robust alternatives. Our top choice is Excel’s new powerful XLOOKUP function.

XLOOKUP is the best alternative but unfortunately, it is available only in Office365.

Why is XLOOKUP better than VLOOKUP?

1. XLOOKUP is a much more functional formula. Just by using XLOOKUP, you can do anything VLOOKUP and HLOOKUP can do. You can also use XLOOKUP’s optional fields to do error handling -which requires nested formulas in VLOOKUP case-, find the next smaller item or bigger item, to look for the last occurrence, even a wild character match! All this and much more could be done in one single formula!
2. As mentioned above, your lookup column cannot be to the left of the column with search value in VLOOKUP. You can search both to the right and left of the lookup value by using XLOOKUP.
3. VLOOKUP will give you an error when you insert a row and change column orders, particularly when you hardcode column number. XLOOKUP compares ranges, you don’t have to define any column numbers with XLOOKUP.
4. VLOOKUP’s 4th argument, range_lookup, use approximate matching by default. If you don’t enter the optional 4th argument as 0 or FALSE (exact match) and your data is not sorted, you will get the wrong result. XLOOKUP doesn’t have this problem. XLOOKUP will look for an exact match by default even if you don’t enter the match_mode argument.
5. You can get very creative with XLOOKUP. You can fully replace an INDEX(MATCH) formula as well as using it with other functions, such as SUM.

Here is an example where we can easily get the total inventory quantity for all the products with ProductID between 1 and 316 by combining XLOOKUP function with SUM.

There you go and problem solved! If you want a more in-depth explanation of how XLOOKUP works Serdar is ready to help you.

How would you have solved this problem?

As we have mentioned there are many alternative ways of solving this case. How would you have solved it? Besides this case, have you already started to use XLOOKUP? If yes, how do you use it?

In our next article, we are discussing the remaining 10 alternatives of VLOOKUP. If you want to look at more details of how we solved this case, we are also going to share an Excel file with all the examples with you. Stay tuned! Please also tell us which Excel formula would you like us to dig into next?

This was the tenth article in the Excel series teaching you how to increase your productivity working in Excel. You can find previous articles below.

Are You Ready To Power Up Your Excel Game?

Here Are The Top 7 Mistakes You Make In Excel. Should We Try And Fix Them?

I Use Excel For Everything But Should I?

Excel Is Like The Grown-Up Version Of A Kindergarten Sandbox

15 Must-Have Habits To Master Excel

A Good Excel Sheet Never Starts At A1

The Simple Functions And Techniques That'll Save Your Excel Sheet

Pssst, Do You Want Some Excel Cheat Codes?

Here Are The Top Excel Features For Any Modeller To Use!

Continue reading below for more articles about how digital is impacting Finance.

Inevitable Retraining Of Finance Professionals Is On The Horizon

Why The Digital Revolution Hasn’t Caught Onto Finance Yet

Tech vs. People. Where Should Finance Invest?

A Digital Reality Check Of The Finance Function

How To Make Robots A Part Of The Finance Family?

Why You Should Only Robotize Standard Processes 

Robots and Humans. A Marriage Made In Heaven Or Hell?

A Tale Of Robots: From Assembly Lines To Knowledge Workers

Robots Must Solve Business Pains To Be Successful

What AI Competencies Do Your Finance Team Really Need?

Here's How To Test If Your AI Solution Will Be A Success

You're The User Of AI. Yes You, So Take Charge!

Blip. Blop. Accounting Robot. Are You Ready?

Are You Ready For Robotics Process Automation?

Have You Met Your Robot Accountant Yet?

Robots Are The Future Of Analytics

Your Robot Accountant Has A Name, It's Dixie

Anders Liu-Lindberg is the co-founder, COO (Chief Operating Officer), and CMO (Chief Marketing Officer) at the Business Partnering Institute and owner of the largest group dedicated to Finance Business Partnering on LinkedIn with more 8,000 members. I have ten years of experience as a business partner at the global transport and logistics company Maersk. I am the co-author of the book “Create Value as a Finance Business Partner” and a long-time Finance Blogger with 40.000+ followers.