"

Data Analysis in Microsoft Excel

Chapter 10 – Retrieval Functions

INTRODUCTION

There are many cases where we have to show data from a range of cells based on what we have in another cell. We are able to “lookup” this data using several Excel functions.

  • Use VLOOKUP to lookup information in a left-most column to retrieve data in a table
  • Use HLOOKUP to lookup information in a top-most row to retrieve data in a table

Guided Exercises
More Practice
Retrieval Functions

Guided Exercises

Open the Chapter10_RetrievalFunctions file. A list of retrieval function explanations is at the end of the chapter.

Employees worksheet

The Employees sheet contains data related to Haptown Employees, with some of the data missing. A list of EmployeeIDs and Departments is in I3:J98 and a list of the Departments and their associated Buildings is in L3:M13. While we could easily pull this from the database, we can also combine this data in Excel.

  1. In cell F4, use a VLOOKUP to return the correct department for the employee from the table in the range L3:M13. Fill the function down column F to return each employee’s department.
  2. In cell G4, use a VLOOKUP to return the correct building for the employee in row 4 from the table in the range L3:M13. Fill the function down column G to return each employee’s building.

Orders worksheet

The Orders worksheet contains a table of data related to the 2024 Sales of items at a company. Each transaction is listed by the order number. The Subtotal, in column D, has already been calculated for you. For every $500 that is spent, the customer receives a discount as shown by the table in cells H4:I36.

  • In cell E4, use a retrieval function to return the correct discount rate from the table in H4:I36. The discount rate for an order is based on the subtotal amount in column D. Format as a Percentage with 1 decimal place displayed. Fill the function down column E to retrieve the discount rate for each order.

Notice that the Grand Total in column F automatically recalculates the total for each order based on the discount rate that has now been retrieved.

Discounts&Shipping worksheet

The Discounts&Shipping worksheet contains the same 2015 Sales data that we used on the Orders worksheet. However, the manager does not want to have so many different discount rate values as the other worksheet had. The table in B4:F6 contains a much simpler way to decide which discount rate an order receives.

  1. In cell E9, use an HLOOKUP to return the discount rate for each transaction using the data from the table in B4:F6. The discount rate for an order is based on the subtotal amount in column D. Format as a Percentage with 1 decimal place displayed. Fill the function down column E to retrieve the discount rate for each order.
  2. In cell F9, use another HLOOKUP to return the shipping fee for each transaction using the subtotal amount in column D and the table in B4:F6. Format the shipping fee values as Accounting and fill the function down column F to retrieve the shipping fees for each order.

Software Accountability worksheet

The Software worksheet has a list of the software departments in the city of Haptown license. The finance people want to know to which vendors all the money is going and what type of licensing is being used.

  1. In cell D4, use an XLOOKUP to return the vendor from cells G3:I40, then fill this down. Adjust column widths as necessary.
  2. In cell D5, use an XLOOKUP to return the license type from cells G3:I40, then fill this down. Adjust column widths as necessary.

If you are going to stop working here, make sure your file is saved. When you close it, if there is any unsaved work, Excel will prompt you to save. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.

More Practice

Work you should do before our next class

Open the Chapter10_RetrievalFunctions file.

The StationeryOrders worksheet contains data related to the sale of stationery items. The calculation in Column G, each order’s subtotal, has already been done for you. However, the store offers discounts based on the item being purchased, the region in which the customer lives, and how many units the customer buys.

  1. In cell H4, use a retrieval function to return the discount amount based on the item that is being purchased in cell D4. The discount amount can be found in the Item Discount table. Fill the function down column H to retrieve the item discount for each order. Format the Item Discount values as Accounting.
  2. In cell I4, use a retrieval function to return the discount amount based on the region in which the customer is located, as indicated in cell B4. The discount amount can be found in the Region Discount table. Fill the function down column I to retrieve the region discount for each order. Format the Region Discount values as Accounting.
  3. In cell J4, use a retrieval function to return the discount amount based on the quantity of items that are being purchased in cell E4. The discount amount can be found in the Quantity Discount table. Fill the function down column J to retrieve the quantity discount for each order. Format the Quantity Discount values as Accounting.
  4. In cell K4, write a formula to calculate the Grand Total for the order in row 4. This should be the Subtotal minus all discounts. Fill the formula down column K to calculate the Grand Total for each order.
  5. In cell K48, use a function to find the total revenue made from all the transactions.

The Grades worksheet contains data related to a student’s grades for one semester. The student’s courses, each course’s credit hours, and the student’s predicted grade earned in the courses are listed in A8:C12. Above this is a table that contains the GPA for each letter grade earned, according to university policy.

  1. In cell D8, use a retrieval function to return the GPA earned for the course in cell A8 based on the grade earned in cell C8. Fill the function down to cell D12 to retrieve the GPA earned for all five of the student’s courses.
  2. In cell E8, write a formula to calculate the number of credit points this student has earned for each course. The number of credit points is the product of the credit hours and the GPA for the course. Fill this formula down through cell E12 to find the number of credit points earned for each course.
  3. In cell F8, use a retrieval function to return the course title from cells P7:Q12, using the course in A8. Make sure you fill this down to list all titles.
    • Hint: Can you use a VLOOKUP?
  4. In cell B14, use a function to find the total number of credit hours this student has during this semester.
  5. In cell E14, use a function to find the total number of credit points earned by this student during this semester.
  6. In cell C17, use a formula to calculate the student’s overall GPA for this semester. This semester’s GPA is found by dividing the total credit points by the total hours for this semester.
    • Note the XLOOKUP in C18 which is using a couple extra arguments to specify a different type of lookup. These cells are showing the “average” grade for the GPA calculated.

You may now save and close your Excel workbook. Make sure the most recently worked file is in your IU OneDrive with the proper date modified timestamp.

Retrieval Function Help

VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: The known or given value that you want to find a match to in the first (leftmost) column of the data. In this example, that would be a Supplier ID. Note that if you use a cell reference to specify the lookup_value argument, that cell reference should be located outside the range of cells specified in the table_array argument.
  • table_array: The two-dimensional (multiple rows and columns) range of data from which you wish to retrieve values.
  • col_index_num: The column number, within the table_array, from which you want to output a value. The leftmost column in the table_array is considered column 1, even if it does not correspond to column A on the worksheet. In this example, our col_index_num would be 4 since that column in our data contains the Phone number.
  • range_lookup: Here you specify either TRUE or FALSE. A range_lookup of TRUE indicates that when looking down the leftmost column, Excel only needs to find an approximate match to the lookup_value. In other words, it’s enough for Excel to find the range of numbers that the lookup_value falls within (e.g., between 1 and 10, between 11 and 20, etc.). A range_lookup of FALSE indicates that when looking down the leftmost column, Excel must find an exact match to the lookup_value. If you omit this argument, Excel assumes TRUE.

HLOOKUP

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • lookup_value: The known or given value that you want to find a match to in the top row of the data. In this example, that would be an Order Quantity. Note that if you use a cell reference to specify the lookup_value argument, that cell reference should be located outside the range of cells in the table_array specified in the next argument.
  • table_array: A two-dimensional (multiple rows and columns) range of data from which you wish to retrieve values.
  • row_index_num: The row number, relative to the table_array argument, from which you want to output a value. The topmost row in the table_array is considered row 1, even if it does not correspond to row 1 on the worksheet itself. In this example, our row_index_num would be 2 since that row in our data contains the Price.
  • range_lookup: Here you specify either TRUE or FALSE. A range_lookup of TRUE indicates that when looking across the top row, Excel only needs to find an approximate match to the lookup_value. In other words, it’s enough for Excel to find the range of numbers that the lookup_value falls within (e.g., between 1 and 10, between 11 and 20, etc.). A range_lookup of FALSE indicates that when looking across the top row, Excel must find an exact match to the lookup_value. If you omit this argument, Excel assumes TRUE.

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array)
  • lookup_value: The known or given value that you want to find a match to in the data
  • lookup_array: The column (or row) of data which will contain the value above, from which you will obtain a “slot” number
  • return_array: The column from which you will return a value that corresponds to the same “slot” number you found above

There are other very useful arguments here, but we will not explore them in this class.

 

License

K160: Introduction to Business Decision Making 2e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.

Share This Book