Learning Microsoft Excel 2007 - Lookup Commands

Learning Microsoft Excel 2007 By Greg Bowden Guided Computer Tutorials www.gct.com.au Lookup Commands Chapter 12PUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided. First published 2007 ISBN: 1 921217 44 8 (Module 1) 1 921217 45 6 (Module 2) PDF document on CD-ROM Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.© Guided Computer Tutorials 2007 12-1 Chapter 12 Lookup Commands The LOOKUP COMMAND allows you to copy data from a table and insert that data in other parts of the spreadsheet. It saves time re-entering the same data over and over. To look up information from a table you need to use the LOOKUP function. There are two different types of LOOKUP functions, VLOOKUP for searching vertically in columns and HLOOKUP for searching horizontally across rows. We will use the VLOOKUP function in this chapter, but the HLOOKUP function works in the same way. Interest Rate Calculations Many banks offer varying degrees of interest depending on the amount deposited in the account. We can use the LOOKUP function to display the correct amount of interest on any entered deposit. A Loading the Template 1 Load Microsoft Excel 2007 and click on the OPEN icon in the QUICK ACCESS TOOLBAR or in the OFFICE BUTTON. 2 Access the EXCEL 2007 SUPPORT FILES, open the CHAPTER 12 folder and load the INTEREST TABLE file, selecting YES to the READ-ONLY message. 3 In cell B2 enter the value: $18500.00. This is the balance that we are going to calculate interest on.© Guided Computer Tutorials 2007 12-2 Learning Microsoft Excel 2007 B Calculating the Interest Due We need to use the LOOKUP function to find the interest due on the balance. Microsoft Excel will look up the table and find the EQUAL OR CLOSEST LOWER value to the balance ($15000). NOTE: The formula says: look up the value stored in cell B2 then look at the values in the table, find the cell with the EQUAL or CLOSEST VALUE BELOW the B2 value, then display the adjacent value from the second column of the table: the value next to $15000 (that is, 3.5%). 2 The LOOKUP function has three sections: 1 In cell B5 enter the formula: =VLOOKUP(B2,D4:E9,2) and format it to PER CENT with 1 decimal place. Reference Cell What cell to look up. Display Column How many columns across from the first column in the table the value should be taken from. Range Reference Indicates the start and end of the data table. =VLOOKUP(B2, D4:E9, 2© Guided Computer Tutorials 2007 12-3 12 Lookup Commands NOTE: i There should not be any spaces in the function formula. ii If we had wanted the BALANCE ($15000) displayed instead of the RATE, we would have entered: = VLOOKUP(B2,D4:E9,1) the 1 indicates the first column of the table. NOTE: The error message indicates that Microsoft Excel cannot locate the closest lower value in COLUMN D. If possible it is always better to start a lookup table with zero. 3 Change the balance in cell B2 to $5296. The Interest Due should change to 2.5%. 4 Enter a balance below $5000 you will receive the #NA error message. © Guided Computer Tutorials 2007 12-4 Learning Microsoft Excel 2007 5 Experiment with some other balances and try to visualise what the LOOKUP function is doing each time. 6 Close the file without saving. Income Tax Calculations Income tax is calculated on a sliding rate. Using a table and the LOOKUP command is an efficient way to be able to calculate the income tax paid by any income. The income tax table will have five cut-off incomes and a TAX RATE column which refers to the tax rate for the amount of income over the cut-off. For example, if a person earns $25,000, he/she pays $2,652 base tax on the first $21,600 of their income, plus 30% of $3400 (the amount earned over $21,600). A Loading the Template 1 Click on the OPEN icon in the QUICK ACCESS TOOLBAR or in the OFFICE BUTTON. 2 Access the EXCEL 2007SUPPORT FILES, open the CHAPTER 12 folder and load the TAX TABLE file, selecting YES to the READ-ONLY message. 3 In cell B2, enter the Gross Income: $25000. This will be the test income for the calculations.