This week you are getting something different! We’ve created a template for an Income and Expenditure Statement that you can customise with your own information to save you from agonising over how to use Microsoft® Excel® formulae to look up data. We have also provided step by step instructions on how to use it. You are welcome to share the template with anyone that will benefit from it.
Note: Download the template to practice this exercise
Applies To: Microsoft Excel 2007, 2010 and 2013
The accounts transactions data is on the transactions list worksheet. You will find the Income & Expenditure Statement on the monthly income statement worksheet.
1. To view the Income & Expenditure Statement for a specific month, click the drop down arrow in cell A1.
2. The values displayed will be extracted from the transactions list worksheet.
3. We used the formula =VLOOKUP(A6,ACCSUM,MATCH($A$1,Headings,0),0) to lookup values from the transactions list worksheet.
a. A6 is the lookup value.
b. ACCSUM is the named range for the data range A1:I20 on the transactions list worksheet, it is also the data table where we lookup data.
c. Instead of manually counting the column index number on the transactions list worksheet we used the Match function to return the column index number for the Vlookup formula.
d. Headings is the named range for the data range A1:I1 on the transactions list worksheet $A$1 is the lookup value and 0 is the exact match.
e. Months is the named range for the months on the transactions list worksheet (M1:M6).
You can download the sample template and customize it using your data.
