• Unit 4: Lookup Functions

    In this unit, we focus on lookup functions, which let you find a given value from a large data set 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 from a website to your worksheet. 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 2 hours.

    • Upon successful completion of this unit, you will be able to:

      • use the VLOOKUP, HLOOKUP, and XLOOKUP functions;
      • create a web query that imports 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 data set in another workbook and display the value where you want. Let's begin by exploring two main lookup functions: the VLOOKUP and the HLOOKUP.

      • Lookup Functions Page

        Use the VLOOKUP function to search for data in vertical columns and the HLOOKUP function to search for data in horizontal rows.

        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.

      • The Benefits of XLOOKUP Page

        The XLOOKUP function is an update to VLOOKUP and HLOOKUP that can search data horizontally or vertically using the same function. Watch this video on the XLOOKUP function and its optional arguments.

      • VLOOKUP and HLOOKUP Page

        Watch this video for examples of how to use and set up the VLOOKUP and HLOOKUP functions.

    • 4.2: Web Queries

      We often make spreadsheets to track outside data, such as currency exchange rates or the prices for various stocks and bonds, that constantly fluctuate. Rather than researching and re-typing each new rate or price into your spreadsheet every time it changes, you can set your spreadsheet up to automatically update the imported data from the relevant website. This is called a web query and is performed using Excel's Power Query.

      • Creating a Web Query Page

        Watch this demonstration of how to use Power Query to import data from the web into your spreadsheet. This function will update your data automatically as the information on the website changes.

    • 4.3: Goal Seek

      Microsoft Excel's Goal Seek feature helps you determine the inputs of a function to give a specific result. This may seem backward, but Goal Seek is useful in many applications.

      For example, imagine you are a student in a class with four exams. You need an exam average of 90 percent to earn an A in the course. 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 the score you need to earn on your last exam to receive an A in the class. You can probably think of similar business applications for Goal Seek.

      • Using Goal Seek Page

        Watch this tutorial, which demonstrates how to use 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 are easy to copy and share.