Click here to download the Excel sample data file so that you can practice along with me. https://learnaccountingfinance.files.wordpress.com/2021/01/learn-xlookup-excel-shared.xlsx
Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. https://bit.ly/3xjMR8t
Do not forget to subscribe to my Youtube Channel
Learn how to use Excel XLOOKUP and what this function can do for you. In this video, I will share 7 practical ways in which you can use the new XLOOKUP function which serves as a perfect replacement for both VLOOKUP and XLOOKUP.
We start with a real life work situation where you have to respond with data requests quickly. The first situation being where the General Manager is asking for Sales for specific customers within 5 minutes. Not only do you provide him the information requested within 5 minutes, but you go above and beyond to provide additional information that he may be interested in. In the second example, your Manager is asking you to complete and send the sales commission file based on annual sales and commission plan. You use XLOOKUP Match mode functionality to quickly respond to the Manager with the completed sales commission report.
In the third example, you have been asked by the Purchasing Manager to provide him help with pulling the most recent purchase price from a long list of materials and purchase history. You use the Search Mode argument of the XLOOKUP function to reverse the order of the data and provide by material SKU, the most recent purchase price and earn bragging rights.
In the fourth example, the external auditors have asked you to provide information related to customers, in a layout which is the opposite of how your sales data is set up. Knowing well that XLOOKUP can replace horizontal lookup or HLOOKUP, you quickly pull the information in the requested format and respond to auditor’s request.
Finally I share a tip that I have personally been using since the VLOOKUP days which would help you avoid XLOOKUP error, when by mistake the data ranges (lookup array and return array) are not aligned. This tip actually saves a lot of time as well and has been one of my favorite tips.
If you have more questions, or would like to learn about advanced ways of using XLOOKUP, please leave a comment. If you enjoy the information provided in the video, please do not forget to press Thumbs Up
Connect with me:
Learn Accounting Finance