Unit 4: Lookup Functions
In this unit, we focus on lookup functions, which let you find a given value from a large set of data in another worksheet and display the value where you want to. We also introduce the VLOOKUP and HLOOKUP functions and explore how to create a web query
that imports stock prices to your worksheet from a website. You can use web queries to import any data you may need. We also examine how to use Goal Seek to change function inputs to give the desired result.
Completing this unit should take you approximately 1 hour.
4.1: Lookup Functions
Lookup functions allow you to find a given value from a set of data in another workbook and display the value where you want.
Let's begin by exploring two main lookup functions: the VLOOKUP and the HLOOKUP. Use the VLOOKUP function to search for data in vertical columns, and the HLOOKUP function to search for data in horizontal rows.
4.2: Web Queries
We often make spreadsheets using real data that changes over time, such as stock prices. Rather than looking up and re-typing the new stock prices every time you use the spreadsheet, you can import data from a website directly into the spreadsheet. This is called a web query.
4.3: Goal Seek
Microsoft Excel's Goal Seek feature allows you to determine what the inputs of a function must be to give a specific result. This may seem backwards, but there are quite a few applications where goal seek can be useful.
For example, imagine you are a student in a class with four exams. To earn an A in the course, you must have an exam average of 90 percent. After taking three of the four exams, you want to calculate the grade you need on the last exam to earn an A for the course. You can use Goal Seek to determine what your last exam score must be to earn the grade you want.
4.4: Printing Formulas and Functions
Here, we learn how to print formulas in a readable format so they can be easily copied and shared.