Introduction to the VLOOKUP function (Tutorial)
Finally, lookup tables, data tables, and Pivot tables can be used together to create powerful spreadsheets and that is what you will do in this problem. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions.
Open spreadsheet
Problem
In the given spreadsheet, columns A though E represent a data table. However, there are missing values in columns C through E. In cell J1 there is a look containing columns for Product, Product ID, and Unit Price. Your first task is to fill in the missing values in columns C, D, and E using VLOOKUP and the foc Doing this manually would take a long time and be very tedious. The best way is to use VLOOKUP to fill in the first row of missing data and then drag down remaining rows.
In cell C2, populate the Product ID of the first item using VLOOKUP. The value you wish to look up in the lookup table is in Column A, cell A2 to be exact. Th table is cell range $J$1:$L$6. Note that these are absolute cell references since the look up table is in a fixed location. The Product ID is in the second colum lookup table. And finally set the range_lookup parameter to FALSE.
What is the Product ID returned in cell C2?
B00Z9QVE4Q
Now repeat this process for the missing Unit Price in cell D2. What is the unit price returned in D2 (to the nearest cent)?
$ 33.03
And finally, for the first item, calculate the Gross Sales in column E using Order Quantity from column B and Unit Price from Column D. What is the gross sale am cell E2?
$ 891.81
To complete the data table, select cells C2, D2, and E2 together then drag down or double-click the fill + sign in the lower right of those three selected cells. And th you have it. The remaining 147 items in columns C, D, and E have been completed using a lookup table, two VLOOKUP formulas, a simple math formula, and Excel' down feature.
Combining VLOOKUP with a Pivot Table
Now that you have the data table in columns A through E, a Pivot Table can be used to summarize the data on a number of factors.