On the EmployeeAnalysis worksheet, in cell J$23,$ enter a MATCH function that will return the row number of whichever category is selected in cell H$20.$ Use the fill handle to copy the formula down through J$27.$

$=$MATCH$($$H$$20,$$F$$5$:$F$$9,0)$

In cell K$23,$ enter a MATCH function that will return the column number of the EmployeeID in cell F$23.$ Use the fill handle to copy the formula down through K$27.$

$=$MATCH$($F$23,$$G$$4$:$K$$4,0)$

In cell H$23,$ enter an INDEX function, using the range G$5$:K$9$ as the array, and referencing the values in J$23$ and K$23$ for the row and column numbers. Use the fill handle to copy the formula down through H$27.$

$=$INDEX$($$G$$5$:$K$$9,$J$23,$K$23)$

On the CustomerAnalysis worksheet, in cell B$20,$ enter an INDEX function that will retrieve the CustomerID of the customer with the highest TotalSales from the array A$4$:G$16.$ Use the fill handle to copy the formula down through B$23.$

$=$INDEX$($$A$$4$:$G$$16,$MATCH$($MAX$($$E$$4$:$E$$16),$$E$$4$:$E$$16,0),$MATCH$($A$20,$$A$$3$: $G$$3,0))$

On the ShippingCosts worksheet, create a Priority named range for cells A$5$:F$10$ and then create an Express named range for cells A$15$:F$20.$

In cell I$7,$ enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table$\_$array so that the customer can choose Express or Priority shipping. Use Match function to return the column number for the VLOOKUP function.

Change the shipping method from Priority to Express and change the zone number of the destination from Zone $2$ to Zone $5$ to ensure the correct shipping cost is retrieved.

$=$VLOOKUP$($I$5,$Priority,$3,$TRUE $)=$VLOOKUP$($I$5,$Priority,MATCH$($I$6,$A$4$:F$4,0),$TRUE $)$

$=$VLOOKUP$($I$5,$INDIRECT$($I$4),$MATCH$($I$6,$A$4$:F$4,0),$TRUE$)$

On the Transactions worksheet, incorporate the IFERROR function in cells G$10,$ H$10,$ and J$10$ so that a blank value $("")$ or $0$ is returned for the value$\_$if$\_$error. Use the fill handle to copy each formula down through row $30.$

$=$IFERROR$($IF$($NOT$($VLOOKUP$($B$10,$Product$\_$List,$3,$FALSE$)=$"Accessories"$),$"Yes"," No$"),"")=$IFERROR$($VLOOKUP$($B$10,$Product$\_$List,$4,$FALSE$)*$D$10,0)=$IFERROR$($HLOOKUP$($E$10,$$B$$4$:$D$$6,2,$FALSE$),"")$