The VLOOKUP function in Excel is a powerful tool that manystudents are expected to master for business-related
Question:
The VLOOKUP function in Excel is a powerful tool that manystudents are expected to master for business-related careers. Asits name implies, VLOOKUP relies on a "lookup" table. In Excel, alookup table contains unique values in the first column and thenadditional columns contain information about the item in the firstcolumn. See the table below for an example:
Product | Product ID | Unit Price |
---|---|---|
Travel Umbrella | B07NQKQ19S | $ 12.74 |
Bluetooth Headphones | B01NAJGGA2 | $ 34.99 |
Battery Pack | B00Z9QVE4Q | $ 35.99 |
Travel Pillow | B00GXBZM5I | $ 12.35 |
Carry-on Suitcase | B07PB3P1N8 | $ 40.59 |
In VLOOKUP, you tell Excel to scan the first column for the itemyou are interested in and then to go over a certain number ofcolumns to find the information you are looking for. For example,let's say you would like to find the unit price for the TravelPillow using the lookup table above. In Excel, it would gosomething like this:
=VLOOKUP(value to lookup, table location, column of interest,matching type)
=VLOOKUP("Travel Pillow", TABLE LOCATION, 3, FALSE)
In the pseudo-formula above, you are telling Excel, "Find'Travel Pillow' in the first column of the lookup table, then moveover to the third column in the table and give me that value." As aresult, you would get a value of $12.35.
NOTE: The VLOOKUP function takes a fourth argument calledrange_lookup that has two options, TRUE or FALSE. Optionally, youcan specify TRUE if you want an approximate match or FALSE if youwant an exact match of the return value.
Finally, lookup tables, data tables, and Pivot tables can beused together to create powerful spreadsheets and that is what youwill do in this problem.
The data has been collected in the Microsoft Excel Online filebelow. Open the spreadsheet and perform the required analysis toanswer the questions below.
Open spreadsheet
Problem
In the given spreadsheet, columns A though E represent a datatable. However, there are missing values in columns C through E. Incell J1 there is a lookup table containing columns for Product,Product ID, and Unit Price. Your first task is to fill in themissing values in columns C, D, and E using VLOOKUP and the lookuptable. Doing this manually would take a long time and be verytedious. The best way is to use VLOOKUP to fill in the first row ofmissing data and then drag down to fill the remaining rows.
In cell C2, populate the Product ID of the first item usingVLOOKUP. The value you wish to look up in the lookup table is inColumn A, cell A2 to be exact. The lookup table is cell range$J$1:$L$6. Note that these are absolute cell references since thelook up table is in a fixed location. The Product ID is in thesecond column of the lookup table. And finally set the range_lookupparameter to FALSE.
What is the Product ID returned in cell C2?
fill in the blank 2
Now repeat this process for the missing Unit Price in cell D2.What is the unit price returned in D2 (to the nearest cent)?
$fill in the blank 3
And finally, for the first item, calculate the Gross Sales incolumn E using Order Quantity from column B and Unit Price fromColumn D. What is the gross sale amount in cell E2?
$fill in the blank 4
To complete the data table, select cells C2, D2, and E2 togetherthen drag down or double-click the fill + sign in the lower rightof those three selected cells. And there you have it. The remaining147 items in columns C, D, and E have been completed using a lookuptable, two VLOOKUP formulas, a simple math formula, and Excel'sfill down feature.
Combining VLOOKUP with a Pivot Table
Now that you have the data table in columns A through E, a PivotTable can be used to summarize the data on a number of factors.
To create the Pivot Table, select the data in columns A throughE. Next, navigate to the Insert tab and click Pivot Table. Usingthe dialog box, place the Pivot Table in cell J10. Finally, usingthe Pivot Table Fields task pane on the right, create the PivotTable below and fill in the values.
NOTE: For the Sum of Gross Sales column, round answers tothe nearest cent and do not enter a dollar sign ($).
Row Labels | Sum of Order Quantity | Sum of Gross Sales ($) |
Battery Pack | fill in the blank 5 | $fill in the blank 6 |
Bluetooth Headphones | fill in the blank 7 | $fill in the blank 8 |
Carry-on Suitcase | fill in the blank 9 | $fill in the blank 10 |
Travel Pillow | fill in the blank 11 | $fill in the blank 12 |
Travel Umbrella | fill in the blank 13 | $fill in the blank 14 |
Grand Total | fill in the blank 15 | $fill in the blank 16 |
Organizations Behavior, Structure, Processes
ISBN: 978-0078112669
14th Edition
Authors: Gibson, Ivancevich, Donnelly, Konopaske