Course Alert:
If you would like to learn in detail, how to calculate income statement variances and the impact they have on sales $, profit $ and profit margin % and ebitda %, 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://ebitda.thinkific.com/courses/learn

Looking for more personalized help?

Get in touch with me on one of these platforms:
Instagram: https://www.instagram.com/learnaccountingfinance
Facebook: https://www.facebook.com/profile.php?id=100063564513161
Tiktok: https://www.tiktok.com/@learnaccountingfinance
Website: https://www.learnaccountingfinance.com
Subscribe: http://www.youtube.com/c/LearnAccountingFinance?sub_confirmation=1

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 for reporting and analysis which serves as a perfect replacement for both VLOOKUP and XLOOKUP.

Download XLOOKUP practice Excel File and follow along with me:
https://bit.ly/3hJAvAy

Learn more:
How to use pivot tables for financial reporting and analysis: https://youtu.be/j_ugVlcfm0k
Dont have Office 365? Learn how to use Vlookup with dynamic update: https://youtu.be/JhISkMFhrik

00:00 Use XLOOKUP to look up values in columns
00:50 Ranges or array length for XLOOKUP
01:34 Return Array with multiple dimensions (multiple columns Xlookup)
03:03 Lookup columns on the left with Xlookup
04:02 Use Xlookup to calculate sales commissions per commission table
09:45 Xlookup N/A error (Not available error in xlookup or vlookup)
11:03 Use Xlookup search mode to reverse lookup order (recent price)
14:23 XLookup to do Horizontal lookup (old HLookup)
16:16 Problems with XLOOKUP you shoul be Aware of!
17:16 How to Avoid XLookup range mismatch error
18:10 Wrap Up

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

Lets get in touch...

Connect:
https://www.instagram.com/learnaccountingfinance
https://www.learnaccountingfinance.com

Subscribe:
http://www.youtube.com/c/LearnAccountingFinance?sub_confirmation=1

Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #xlookup #xlookup #excel #learnaccountingfinance