1. 2. 3. Select the Tables sheet, select cells A6:B10, and create range names using the Create from Selection button [Formulas tab, Defined Names group]. Select cells B1:F2 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. a. Click the Commissions sheet tab and select cell F5. b. Start the HLOOKUP function and use cell E5 as the lookup_value. C. For the table_array argument, use the H_Rates range. d. Use the second row for the row_index_num argument (Figure 2-100). =HLOOKUP(E5,H_Rates, 2) E G Central Sierra Insurance Commission Sales and Bonuses F5 C H K M N 1 2 Function Arguments 2 3 4. 5 6 7 8 ID Last Name First Name Branch Commissions Bonus Rate 201 Lingle Bob Cameron Park $15,000 Rates 2) 102 McCartney Lanita Folsom $12,000 103 Santos Eleesha Cameron Park $5.500 104 Baxter Roy Granite Bay $4,500 105 Alaro Jennifer Folsom $3,000 115 Chan Tami Cameron Park 53,000 147 Althouse Charlene Granite Bay $4,700 119 Taylor Juan Granite Bay 54,000 120 Reza Wayne Cameron Park 54,000 125 Nevens Cheryl Cameron Park $12,000 HLOOKUP Lookup_value ES = 15000 Table_array H Rates - 3000,6000,9000 12000, 15000;0.015,04 Row_Index_num 2 2 Range_lookup logical - 0.025 Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify Row_index num is the row number in table_array from which the matching value should be returned. The first row of values in the table is row 1. 9 10 11 12 Formula result - 0.025 Help on this function OK Cancel 13 14 15 16 Figure 2-100 HLOOKUP function to display bonus 1. 2. 3. Select the Tables sheet, select cells A6:B10, and create range names using the Create from Selection button [Formulas tab, Defined Names group]. Select cells B1:F2 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. a. Click the Commissions sheet tab and select cell F5. b. Start the HLOOKUP function and use cell E5 as the lookup_value. C. For the table_array argument, use the H_Rates range. d. Use the second row for the row_index_num argument (Figure 2-100). =HLOOKUP(E5,H_Rates, 2) E G Central Sierra Insurance Commission Sales and Bonuses F5 C H K M N 1 2 Function Arguments 2 3 4. 5 6 7 8 ID Last Name First Name Branch Commissions Bonus Rate 201 Lingle Bob Cameron Park $15,000 Rates 2) 102 McCartney Lanita Folsom $12,000 103 Santos Eleesha Cameron Park $5.500 104 Baxter Roy Granite Bay $4,500 105 Alaro Jennifer Folsom $3,000 115 Chan Tami Cameron Park 53,000 147 Althouse Charlene Granite Bay $4,700 119 Taylor Juan Granite Bay 54,000 120 Reza Wayne Cameron Park 54,000 125 Nevens Cheryl Cameron Park $12,000 HLOOKUP Lookup_value ES = 15000 Table_array H Rates - 3000,6000,9000 12000, 15000;0.015,04 Row_Index_num 2 2 Range_lookup logical - 0.025 Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify Row_index num is the row number in table_array from which the matching value should be returned. The first row of values in the table is row 1. 9 10 11 12 Formula result - 0.025 Help on this function OK Cancel 13 14 15 16 Figure 2-100 HLOOKUP function to display bonus
