Now repeat this process for the missing Unit Price in cell D$2.$ What is the unit price returned in D$2($to the nearest cent$)?$

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?$

To complete the data table, select cells C$2,$ D$2,$ and E$2$ 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 J$10.$ 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 $($$$).$

$\backslash$table$[[$Row Labels,Sum of Order Quantity,Sum of Gross Sales $($$$),],[$Battery Pack,$34,$$$,1,206\mathrm{.}32$