Continuing with our previous example, suppose the company has received a new bonus scheme.
Anyone earning up to just under 30,000 does not receive a bonus. From 30,000 to just under 50,000, the employee earns a 5% bonus on their yearly salary. From 50,000 to just under 60,000 they receive an 8% bonus, and so on.
Although we wish to calculate the bonus amount, we first need to determine the applicable bonus percentage. Once the bonus percentage is determined, we will multiply the percentage against the yearly salary to arrive at the bonus amount.
On the “Salary” sheet, select cell E3 and enter the following formula.
=XLOOKUP(B3, B_Master!$E$13:$E$17, B_Master!$F$13:$F$17, , -1)
NOTE: We are skipping the [if_not_found] argument, so we need to make sure we place 2 commas to account for the unused argument.
Because XLOOKUP defaults to “exact match” mode, we are using the “-1” option for the [match_mode] argument. If we fail to locate an exact match of the salary, we want to return the next closest bonus that doesn’t exceed the salary.
The [match_mode] arguments are as follows:
We see that Gary Miller, with a yearly salary of 60,270, receives a bonus percentage of 10%.
60,000 is the closet salary to Gary’s 60,270 without going over.
Fill the formula down to the remaining employees and we see the following results.
Because we wanted to calculate the bonus amount, not find the bonus percentage, we will return to cell E3 and modify the formula to multiply the discovered bonus percentage by the employee’s yearly salary.
=XLOOKUP(B3, B_Master!$E$13:$E$17, $F$13:$F$17, , -1) * B3
Fill the formula down the column to replicate the updated formula.