Calculating vacation entitlement based on service date and grade

Hi,

I am trying to add in formulas in column J and K that would calculate vacation entitlement in days (J) based on employee's grade and years of service as per policy sheet, and then calculate vacation entitlement in hours (K) based on hours they work, their grade and years of service.

Not sure if index match will generate the desired result for this or any other formula? I have provided some examples in columns J and K about the desired results.

Also, if I am flexible in having different layout/structure of policy tab so it could calculate easily and more data friendly.

Thanks so much for helping me out.

Cheers
Rae

One way:

Please change the lay-out of the policy table a little bit and try in J2 and copy down:

Formula:

Please Login or Register  to view this content.

Glad to have helped. Thanks for the feedback and rep .