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 lookup table 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 lookup table. 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 to fill the 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. The lookup 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 column of the lookup table. And finally set the range_lookup parameter 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 in column E using Order Quantity from column B and Unit Price from Column 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 together then drag down or double-click the fill + sign in the lower right of those three selected cells. And there 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's fill 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.

To create the Pivot Table, select the data in columns A through E. Next, navigate to the Insert tab and click Pivot Table. Using the dialog box, place the Pivot Table in cell J10. Finally, using the Pivot Table Fields task pane on the right, create the Pivot Table below and fill in the values.

NOTE: For the Sum of Gross Sales column, round answers to the 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

Check My Work

Reset Problem