• 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.

    • 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;
      • use Goal Seek to answer a what-if scenario; and
      • print formulas so they are readable.

    • 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.

      • Lookup Functions Page

        Read these two sections on the VLOOKUP and HLOOKUP functions. The input for lookup functions is complicated, and you must be precise. Pay close attention to Tables 3.10 and 3.11, which detail the inputs for the two functions. Also, note the Skills Refreshers, which review the steps for entering the functions in Microsoft Excel.

      • VLOOKUP and HLOOKUP in Microsoft Excel Page

        This video gives examples of how to use and set up the VLOOKUP and HLOOKUP functions.

    • 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.

      • Web Queries Page

        Read the section on web queries, which explains how to import stock prices directly into an Excel spreadsheet. The Skills Refresher summarizes the steps to creating a web query in Microsoft Excel.

      • Creating a Web Query in Microsoft Excel Page

        Watch this demonstration of how to create a web query to use in your spreadsheet. This allows you to insert data that will automatically update as the information on the website changes.

    • 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.

      • Using Goal Seek in Microsoft Excel Page

        Watch this tutorial, which goes through an example of using Goal Seek to tally votes in an election. You can apply Goal Seek in many different ways.

    • 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.