MO-201 Question #23: Real Exam Question with Answer & Explanation

Excel Lookup Formula: Years of Service Bonus Overall Goal The objective is to automate bonus calculation by having Excel look up each employee's years of service against a bonus tier table and return the corresponding bonus amount. This avoids manual entry and ensures consistent,

Create advanced formulas and macros

Question

On the "Employee" worksheet, in cell F4, enter a formula that returns the employee's bonus from the 'Years of Service Bonus' table. Adjust the formula and then copy it to cells F5-F19.

Explanation

Excel Lookup Formula: Years of Service Bonus

Overall Goal

The objective is to automate bonus calculation by having Excel look up each employee's years of service against a bonus tier table and return the corresponding bonus amount. This avoids manual entry and ensures consistent, error-free results across all employees.

The correct tool is VLOOKUP (or XLOOKUP in newer Excel versions) with approximate match, because bonus tables are typically tier-based - e.g., "0–2 years = $500, 3–5 years = $1000" - not exact-match lists.

Step-by-Step Breakdown

Step 1: Identify the components before writing the formula

Lookup value: The employee's years of service, likely in column E (e.g., E4)
Table array: The 'Years of Service Bonus' table range (e.g., on another sheet or a named range)
Column index: Which column in the bonus table holds the bonus amount (typically 2)
Match type: TRUE (approximate match) for tier/range-based tables

Skipping this analysis leads to referencing the wrong column or using exact match (FALSE), which returns #N/A for any year count not explicitly listed.

Step 2: Enter the formula in F4

=VLOOKUP(E4, BonusTable, 2, TRUE)

Where BonusTable is the range of the 'Years of Service Bonus' table. This returns the bonus for the first employee.

E4 = this employee's years of service
BonusTable = the two-column lookup table (years | bonus)
2 = return the 2nd column (the bonus amount)
TRUE = approximate match - finds the largest value ≤ the lookup value

Using FALSE (exact match) would fail unless every possible year count appears in the table. Tier tables never list every individual year.

Important: The bonus table must be sorted in ascending order by the first column for approximate match to work correctly. If unsorted, VLOOKUP returns wrong results silently - no error, just bad data.

Step 3: "Adjust the formula" - make the table reference absolute

Change the table array reference to use absolute references with $:

=VLOOKUP(E4, $H$2:$I$10, 2, TRUE)

(Using whatever the actual table range is)

E4 stays relative - it should shift to E5, E6… as you copy down
$H$2:$I$10 stays absolute - the table must not shift when the formula is copied

If you skip making the table reference absolute, copying the formula down causes the table range to shift (H3:I11, H4:I12…), referencing wrong or empty cells and returning errors or incorrect bonuses.

Step 4: Copy the formula to F5:F19

Select F4, then copy and paste (or fill down) to F5:F19. Each row now looks up that row's years of service against the same fixed bonus table.

If you copy before fixing the references (Step 3), you'd have to manually fix 15 broken formulas.

What Goes Wrong If Steps Are Out of Order

Mistake	Result
Using exact match (`FALSE`)	`#N/A` errors for most employees
Forgetting `$` on table range before copying	Wrong bonus amounts or errors in F5:F19
Referencing wrong column index	Returns years-of-service value instead of bonus
Table not sorted ascending	Incorrect bonuses with no visible error

Memory Tip

"Lock the table, free the lookup."

The lookup value (E4) must be free to move down each row. The bonus table must be locked in place. Whenever you copy a lookup formula, ask: "Which part should stay fixed?" - that part gets $ signs.

Topics

#Lookup functions#Cell referencing#Formulas#Data retrieval

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice Browse All MO-201 Questions