Live Course on Drafting of Appeal and Reply of Penalty Notice by CA Sachin Sinha. Register Now!!

Vlookup

shreyansh (Student) (135 Points)

16 November 2016

Can somebody please tell me what is the use of vlookup formula for preparing Balance sheet from Trial balance? I can show you a sample file i received. I want to know, what is the use of vlookup in it? I am a beginner.

Reply
Follow
Share

5 Replies

shreyansh (Student) (135 Points)
Replied 16 November 2016

Attached File : 102133 20161116122619 financial statements sept 2016.xlsx downloaded: 254 times

CMA Poornima Madhava (CMA) (13097 Points)
Replied 16 November 2016

MIS or any other financial statement prepared in excel is usually linked to the Trial balance prepared using accounting softwares like Tally etc.
Assume that you have a specific MIS report format and the data to be entered into the same is to be taken from TB
In such a case, using Vlookup function you can link the value in the TB to the MIS report. In other words, there is no need for anyone to enter the data manually into the MIS report. You may do n number of corrections in the MIS report just by overwriting the TB sheet.
In short, it is a quick way to prepare reports based on some existing data which is dynamic.

shreyansh (Student) (135 Points)
Replied 20 November 2016

Can you please look at my attachment and explain me in detail what utility vlookup is giving in it? Sorry, I am absolute beginner. Thanks a ton in advance!

CMA Poornima Madhava (CMA) (13097 Points)
Replied 24 November 2016

Take for example, trial balance contains income, expense, asset & liabilities account
Suppose, you want to extract only income and expense data from TB into P&L account
For instance, you want to get sales figure from TB and show that figure in the Sales column in the P&L account (which is a separate sheet in Excel)
If direct reference is given (="cell reference"), then for the time being the formula can help in linking the value in the TB to P&L. But when the data in TB changes (like new row is added/deleted either due to new entry or removal of entry), then cell reference (say for sales) also changes and so will the value.
In order to get rid of this problem, vlookup function is used. Here the reference used is the name of the account head (in our example, say Sales)
Excel would look for account head called Sales and it would pick up the value for that particular row (where sales account head appears) from the relevant column and shows that figure in the P&L account
Even when the row of Sales is changed in Excel, excel would still provide right figure because the reference here would be the name of the account (rather than cell reference)
Hope this is clear to you
If not, I suggest you to google for examples of vlookup function so that you get better idea.