Chapter 6 – 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

Open the Chapter06_RetrievalFunctions file.

Products worksheet

This sheet contains data related to the sales of products in 2015. However, the department and category of the product are not listed, and these pieces of data would be helpful to have so that the sales data can be analyzed by department or category. A list of the products with their departments and categories is in the range H4:K89.

  1. In cell E4, use a retrieval function to return the correct department for the product in cell A4 from the table in the range H4:K89. Fill the function down column E to return each product’s department.
  2. In cell F4, use a retrieval function to return the correct category for the product in cell A4 from the table in the range H4:K89. Fill the function down column F to return each product’s category.

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.

Orders worksheet

This contains a table of data related to the 2015 Sales of items. 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

This sheet 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 a retrieval function 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 a retrieval function 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.

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.

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.

More Practice

Work you should do before our next class

Open the Chapter06_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.

Now that the revenue amounts are finalized, you can analyze the orders by item and region.

  1. In cell R5, use a function to find the number of orders in which the item in cell Q5 was purchased. Fill the function down through cell R9 to return the number of orders for each item.
  2. In cell S5, use a function and the item in cell Q5 to find the total number of those items that were sold. Fill the function down through cell S9 to return the total number of each item sold.
  3. In cell T5, use a function to find the total revenue made from the sales of the item in cell Q5. Fill the function down through cell T9 to return the total revenue for each item. Format the Total Revenue values as Accounting.
  4. In cell U5, use a function and the item in cell Q5 to find the average revenue from each order of that item. Fill the function down through cell U9 to return the average revenue per order for each item. Round the Average Revenue per Order values to 2 decimal places and format as Accounting.
  5. In cell R13, use a function to find the number of orders from the region given in cell Q13. Fill the function down through cell R15 to return the number of orders from each region.
  6. In cell S13, use a function to find the total number of items sold in the region given in cell Q13. Fill the function down through cell S15 to return the total number of items sold in each region.
  7. In cell T13, use a function to find the total revenue made from the sales of items in the region given in cell Q13. Fill the function down through cell T15 to return the total revenue for each region. Format the Total Revenue values as Accounting.
  8. In cell U13, use a function to find the average revenue per order in the region given in cell Q13. Round the Average Revenue per Order values to 2 decimal places and format as Accounting. Fill the function down through cell U15 to return each region’s average revenue per order.

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. For your information, IU’s Grading System and GPA calculation policies can be found at this site.

  1. Feel free to change the courses, credit hours, and grade earned values to be your own classes. You’ll have to estimate the grade you might earn in your courses, but that’s okay and can always be changed later if you wish to continue using this worksheet to calculate your GPA.
  2. 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.
  3. 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.
  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.

License

K160: Introduction to Modeling and Solving Business Problems 1e Copyright © 2024 by Trustees of Indiana University. All Rights Reserved.