$2$ On the SalesData worksheet in cell B$4,$ use an XLOOKUP function to retrieve the product name for the ProductID in cell A$4,$ using the table of data about the products at Kelly Computers on the Data worksheet. If the value is not found, display a blank $("").$ Use the AutoFill handle to copy the function down to cell B$18.$ Use the AutoFill Options to Fill Without Formatting.

$3$ In cell C$4,$ use a VLOOKUP function to retrieve the Unit Price for the ProductID in cell A$4,$ using the table of data about the products at Kelly Computers on the Data worksheet.

Copy the formula to cells C$5,$ C$7$:C$9,$ C$11$:C$12,$ C$14$:C$15,$ C$17$:C$18.$

$4$ On the SalesData worksheet, in cell G$4,$ create a formula that will perform a calculation that multiplies the Unit Price by the sum of products sold for January, February, and March. If a product has not sold any units over the past three months, display Discontinue. If the project has sold units, display the results of the Unit Price multiplied by the sum of the products sold for January, February, and March.

Copy the formula, and then paste it in cells G$5,$ G$7$:G$9,$ G$11$:G$12,$ G$14$:G$15,$ and G$17$:G$18.$ Resize the column as needed.

$5$ In cells D$6$:G$6,$ D$10$:G$10,$ D$13$:G$13,$ D$16$:G$16,$ and D$19$:G$19,$ use a function to calculate the total product units and revenue for January through March.

$6$ Your supervisor has requested an area on the spreadsheet that quickly summarizes sales over the past few months. In cell D$25,$ calculate the sum for the Hard Drive category that totals the three months results in D$6$:F$6.$ Use a similar formula in cells D$26$:D$29$ substituting the correct formula cell references for each product.

$7$ In cell E$25,$ create an IF function that will determine if sales goals have been met for each product listed. Return Goal Met if true, otherwise return Not Met. Copy the formula down to cell E$29.$

$8$ In cell I$8,$ enter the FILTER function to display the Monthly Snapshot data for those products that met their goal in Quarter $1.$

$9$ Create a PivotTable on a new worksheet named PivotAnalysis that summarizes the items by the sales goals and actual sales of the items. Add the Totals by Item field to the Rows area, and then add the Goal and Actual fields to the Values area $($in that order$).$ Type Category in cell A$3,$ type Total Goal in cell B$3,$ and type Actual Sales in C$3.$ Apply the Light Grey, Pivot Style Dark $1.$

Create a clustered column PivotChart. Move the chart so the upper left corner is in cell A$15$ and the lower right corner is in the G$30.$ Add the chart title Sales Goals vs Actual Sales and apply the Style $4$ Chart Style.