Select the Tables sheet, select cells A$6$:B$10,$ and create range names using the Create from Selection button $[$Formulas tab, Defined Names group$].$

Select cells B$1$:F$2$ and click the Name box. Name the selection H$\_$Rates. Note that the first row is arranged in ascending order.

Create an HLOOKUP function to display the bonus rate.

Click the Commissions sheet tab and select cell F$5.$

Start the HLOOKUP function and use cell E$5$ as the lookup$\_$value.

For the table$\_$array argument, use the H$\_$Rates range.

Use the second row for the row$\_$index$\_$num argument $($Figure $2-100).$

The formula is $=$HLOOKUP$($E$5,$H$\_$Rates,$2)$

Figure $2-100$ HLOOKUP function to display bonus

Format the results as Percent Style with two decimal places.

Copy the formula in cell F$5$ to cells F$6$:F$14.$

Set order of operations to calculate total earnings.

Select cell G$5.$

Build a formula to add the commissions amount $($E$5)$ to the commissions amount times the rate $($F$5*$E$5).$

Copy the formula in cell G$5$ to cells G$6$:G$14.$

Create and copy a SUMIF function to calculate total earnings by branch office.

Select cell E$18.$

Start the SUMIF function with cells $D$$5$:$D$$14$ as the Range argument.

Set the Criteria argument as a relative reference to cell C$18.$

Select cells G$5$:G$14$ for the Sum$\_$range argument and make the references absolute$.$

Copy the formula in cell E$18$ to cells E$19$:E$20$ without formatting to preserve borders.

Format cells E$18$:E$21$ as Currency.

Total the earnings in cell E$21.$

Create and format the current date.

Select cell G$23$ and insert the NOW function.

Select cell G$23$ and click the Number drop$-$down list, and select More Number Formats. On the Number tab, select the Date category.

Scroll the Type list to find the date that displays the month spelled out, the date, a comma, and a four$-$digit year $($Figure $2-101).$

The Date category in the Format Cells dialog box

Figure $2-101$ Date format selected

Click OK$.$ Press fn$+$Ctrl$+$Left Arrow or fn$+$Cmd$+$Left Arrow.

Create and copy a VLOOKUP function to display goals for each funding source.

Click the Family Day sheet tab and select cell F$6.$

Start the VLOOKUP function and use cell E$6$ as the lookup$\_$value.

Click the Tables sheet tab for the table$\_$array argument and use cells $A$$6$:$B$$10.$ The data is sorted by the first column in ascending order.

Use the second column as the col$\_$index$\_$num The range$\_$lookup argument is empty.

Copy the formula in cell F$6$ to cells F$7$:F$20$ without formatting to preserve the fill color.

Format cells F$6$:F$20$ as Currency with no decimal places.

Create and copy an IF function.

Select cell H$6$ and start an IF

Type a logical$\_$argument to determine if cell G$6$ is greater than or equal to $(>=)$ cell F$6.$

Type Yes as the Value$\_$if$\_$true argument and No as the Value$\_$if$\_$false argument.

Copy the formula in cell H$6$ to cells H$7$:H$20$ without formatting to maintain the fill color.

Center align cells H$6$:H$20.$

Press fn$+$Ctrl$+$Left Arrow or fn$+$Cmd$+$Left Arrow.

Insert a new sheet at the end of the tab names and paste the range names starting in cell A$1.$ AutoFit columns A:B and name the worksheet as Range Names.

Save and close the workbook $($Figure $2-102).$