Contoh Vlookup Untuk Accounting

The raw data consists of budget, forecast and actual data.

raw data

A summary table can be composed to show current month, and year to date, with the user having the ability to choose the month.

summary table

The functions and formula used to do this is firstly creating a period based on the month selected by the user. The user chooses a month which a vlookup function is referenced to. The vlookup searches the below table and allocates a period.

month table

The period in B2, has a vlookup formula which is cell referenced to A2. I have named B2 “period” for ease of using cell at a later time. In cell A2, I have made it a data list. The list is the months shown in the previous table.

Monthperiod

Following this I have then populated the summary table with the following formulas.

For month to date I have used =offset(D10,0,period).   I have used D10 as an example of where the first cell of data is for budget. What the offset function does, is that it moves up or down rows, and the columns from a cell reference. So in this example the value returned will move to the right of the cell D10, every time the period changes. Remember the period has been named, so when the user chooses the next month the value of period will increase by 1 (this is because of the vlookup, right?).

For Year to date, it’s incorporating the above month to date formula with a sum formula. So I use =sum(D10:Offset(D10,0,period)). What this does it that it sums across from Jan to the month that has been selected in the period.