Each account rep is assigned to a tier, which determines whether they receive a salary bonus. Bao wants to list the tier for each account rep according to the Bonus Tiers data in the range K5:L9. In cell H11, enter a formula using the VLOOKUP function. Use a structured reference to the Eval Score (cell G11) as the lookup value. Use absolute cell references to the Bonus Tiers data (range K6:19) as the table_array. Use the Tier column (column 2) as the col_index_num. Do not enter a value for the optional range_lookup argument. If Excel does not copy the formula automatically, fill the range H12:H29 with the formula in cell H11. (Hint: To avoid an #SPILL error, enable implicit intersection by placing the This Row specifier (@character) in front of field names.) How to do this in excel
To accomplish this task in Excel, follow these steps:
1. In cell H11, enter the following formula using the VLOOKUP function:
=VLOOKUP(@G11, $K$5:$L$9, 2)
2. Press Enter to apply the formula to cell H11.
3. If Excel does not automatically copy the formula to the entire range H12:H29, manually fill the range with the formula in cell H11. You can do this by dragging the fill handle (a small square at the bottom-right corner of the cell) down to fill the range.
4. The formula will lookup the Eval Score in cell G11 in the Bonus Tiers data range K5:L9 and return the corresponding Tier for each account rep in the range H11:H29.
5. Make sure to use absolute references for the Bonus Tiers data range ($K$5:$L$9) to ensure that the formula works correctly when copied to other cells.
6. Enable implicit intersection in the formula by using the @ character before the field names to avoid #SPILL errors.
By following these steps, you can list the tier for each account rep according to the Bonus Tiers data using the VLOOKUP function in Excel.