Unit 5: Lookup Functions and Review of Functions This unit will introduce the VLOOKUP and HLOOKUP functions as well as how to create a web query that imports stock prices to your worksheet from a website.
Unit 5 Time Advisory
Completing this unit should take you approximately 3 hours.
☐ Subunit 5.1: 1 hour
☐ Subunit 5.2: 0.5 hours
☐ Subunit 5.3: 0.25 hours
☐ Subunit 5:4: 1.25 hours
Unit5 Learning Outcomes
Upon successful completion of this unit, you will be able to: - use the VLOOKUP and HLOOKUP functions; - create a web query that imports stock price data from a website; and - use Goal Seek to answer a what-if scenario.
5.1 Lookup Functions
- Reading: How to Use Microsoft Excel®: “Section 3.3”
Link: How to Use Microsoft Excel®:
Instructions: Read Chapter 3, Section 3.3: Lookup Functions, which starts on page 265. You will continue to use the follow-along file from Unit 4 above (Excel® Text Chapter 3) as you finish this chapter.
Completing this assignment should take approximately 1 hour.
5.2 Review of PMT using Goal Seek
- Web Media: GCFLearnFree.org’s “Excel® 2010: Using What-If
“Excel® 2010: Using
Instructions: Go through the 5 pages, watching the video on page 2. This review uses the PMT function that you learned in subunit 3.3. The last page also introduces Scenarios and Data Tables. The class will not dig any deeper into these tools but you can use Help to learn more if these are features you would find useful. If you have Excel® 2010, when you have finished, there is a practice document at the end of this presentation that you can open and follow the instructions to practice the skills you learned. If you have an older version of Excel®, open your version and create the spreadsheet you see on page 3. Find Goal Seek in your program to practice what you learned here.
Completing this assignment should take approximately 30 minutes.
5.3 Printing Formulas and Functions So now you know a lot about writing formulas in Excel®. If you want to share your formulas with someone in a printed format, you will need to be able to see them in the spreadsheet for them to print.
- Reading: The Saylor Foundation’s “Printing Formulas”
Link: The Saylor Foundation’s “Printing
Instructions: Read the PDF about printing formulas. Then, open your own spreadsheet program and turn on the Formulas view and try printing it to one page.
Completing this assignment should take approximately 15 minutes.
5.4 Exercises for Unit 5
- Activity: The Saylor Foundation’s “Practice Exercise for Unit 5”
Link: The Saylor Foundation’s “Practice Exercise for Unit
Instructions: Open the accompanying PDF document and follow the directions to complete the Practice for the skills you just learned. This file is quite comprehensive and it reviews many of the functions you learned in Chapters 3-5. Think of it as a test of what you have learned so far.
Completing this assignment should take approximately 1hour and 15.