We apply bonuses based on two criteria: years of service and an annual assessment level. For example - if an employee has 10 years of service and he received an 'Exceptional' assessment, he'd earn a 15% bonus. If two employees each had 4 years experience but one received an 'Exceeds' and the other a 'Meets', the higher score would earn an 8.5% and the other 7.7%.
I need a formula that will look up the years of service for each employee, and then their assessment score to return the bonus value they should receive. I've tried a few lookup options but I can't find anything that suits my specific needs of multiple criteria returning one value.
Please see below for data.
[COLUMN A] [B] [C] [D]
Years of Service Exceptional Exceeds Meets
<2 3.85% 3.25% 2.75%
2 7.70% 6.50% 5.50%
4 9.60% 8.50% 7.70%
6 11.50% 10.50% 9.50%
8 13.25% 11.50% 10.00%
10 15.00% 13.50% 12.00%
[COLUMN A][B] [C] [D]
Employee Years Assessment Bonus %
A 10 Exceptional [Note]: must match YOS and 'Exceptional' % level (15.00%)
B 4 Exceeds
C 4 Meets
D 2 Exceeds
E 2 Exceeds
F 6 Meets
G 6 Exceptional
H 8 Meets
Bookmarks