Formula needed for payroll worksheet calculation - index/match, vlook, other?

Hello Excel forum community. I'm developing a payroll cost worksheet for a disability insurance plan, and I need a formula that will do a few different look ups and calculations and return the correct figure given my look up requirements.

I need the formula to do the following (using the attached sample workbook as a guide):

1. If there is a Class # in Column E, it needs to match that class # with the respective Class # column (column J-N). Class #’s can be anywhere from 1-5.

2. Then, once it’s matched that class # to the appropriate column J2-N2, it needs to take the rate found in the matched column Range (rows 37-135) that correlates with that person’s age found in Column D, multiply that rate by the STD Benefit amount for that person found in column F and divide by 10.

3. Lastly, the formula should account for if there is no class # in column E for a person, or if their is language in Column G such as Not Enrolled or Ineligible, or Excluded, or Waived, $0 should be the value returned in column G for that person.

Example using the attached sample spreadsheet:
Employee # 1 (in column 3) has a $349 weekly benefit. The Monthly billed premium for this 43 year old, Class 1 employee should be ($0.45 x $349)/10 = $15.71. $15.71 should be the value this formula returns in Cell G3.

Be Well,

Thank you! That worked. I just needed to add the math equation at the end of the formula to get what I was looking for.

be well...