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

How Vlookup Column Reference updates automatically with a change in Original data table or Source data , such as addition or deletion of a column.

Advance pivot table tricks for reporting and analysis: https://youtu.be/j_ugVlcfm0k
Fastest way to convert numbers stored as text: https://youtu.be/_OpzVbw7tkw
Create automated waterfall chart: https://youtu.be/K-cPZjA8EzE
Are you a First Time or New Manager? Check this out: https://amzn.to/35qWzLc

You will learn two completely different ways in which you can make the column reference in vlookup automated and dynamic, so that you never have to worry about adding or deleting a column in the original data set. Many of the my old Excel files had this issue. I would use a hard coded column number when using the vlookup function (e.g. if I am looking up data in the third column of a data table, I would use the number 3). This was risky and sometimes resulted in painful realization that the lookup values are not correct, when somebody, or even I myself added a few columns to the original data, not realizing what it will do the lookup in another sheet or file.

Learn pivot table data crunching business skills here: https://amzn.to/3lgYB5E
Learn all about Excel in one place: https://amzn.to/3laCXQx

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

To resolve this issue, and have a peace of mind, I share two completely distinct ways in which you can use the vlookup formula, by adding another formula where the column number is hard coded.

The first method is when you have already decided which column from the data set, you want to lookup from. In this case I recommend using the "columns" function. This function will automatically update the column reference number in the vlookup formula even if you add or delete columns on the original data set. This should work seamlessly if the data table and the lookup cells are in the same file. One note of caution here though! if you are looking up information in another Excel file, you will have to open the file to update the formula, or you can click on Data, Edit Link and then update values so that the correct column is chosen.

The second method is when you want to make the column selected for lookup dynamic. You can use for example a drop down menu to lookup information from different columns in the data set. In this case, I recommend the use of "match" function, linked to the drop down cell, so that as you select a different column from the drop down menu, the column reference in the vlookup formula is updated automatically, again ensuring you pick information from the correct column.

Using any of these methods will save you countless hours of correction and fixing of the vlookup formula in large files where there are lots of vlookup reference. I wish I had used the first method when preparing the standard cost model. I could easily modify the financial model without worrying about the impact on the vlookup results.

Let me know what you think about the video and the tips shared by leaving a comment. If you have not subscribed already, I would love to have you in my circle, so please subscribe now.

Thanks
Learn Accounting Finance