Selected Answer
The solution lies in the realisation that you don't have 3 brackets but 4.
- Below 55
- Above 55
- Above 60
- Above 65
Pay close attention to the meaning of "below 55". It excludes someone who is 55. Therefore the following descriptions are wrong. They shouldn't be "Above 55" but "55 and above" because the employee who is 55 - and therefore excluded from the first group - must be included here.
So, I created a named range on your sheet "CPF rate" and called it AgeBrackets. It doesn't matter where this range is but it must have 4 cells with the numbers 1, 55, 60 and 65 in them. The first number could/should be 0. It's insignificant but the bracket "below 55" must start somewhere.
Now, I applied the Custom cell format 0 "and above" to cells 2:4. To cell #1 I applied a custom format "Normal". That simply has the effect that whatever number you enter the display will be "Normal" but for calculation and lookup purposes the cell retains the value of the entered number, here 1 or 0. So, now you have a range by the name of AgeBrackets with the values 1, 55, 60, 65 which are displayed as
Normal
55 and above
60 and above
65 and above
Since you have 4 brackets, what are the associated CPF rates?
- Normal = 20%
- 55 and above is 13%
- 60 and above is 7.5% and
- 65 and above is 0% (I presumed)
This range of 4 cells I gave the name of E_CPF (for Employee's CPF) and I created a corresponding range C_CPF (for Company CPF) to hold the rates payable by the employer.
Now your formulas in H2 and K2 can be
[H2] =INDEX(E_CPF,MATCH($D2,AgeBrackets,1))*SUM($E2:$G2)
and
[K2] =INDEX(C_CPF,MATCH($D2,AgeBrackets,1))*SUM($E2:$G2)
Observe that I used absolute column addressing so that the formula can be copied from column H to column K and only one letter changed.
Read up on the MATCH function before you set up a similar arrangement for the SFD rate. I recommend to avoid the TRUNC function. Use ROUND() instead.