Formula to calculate a rate based on every 1,000 of payroll

Hello,

I am trying to factor in a column into the attached spreadsheet that will multiply an expense rate/$1,000 of wages. Here is what the table I am referencing looks like......

Age Band Rate/$1,000

Through 29 $.14

30-39 $.18

40-44 $.34

45-49 $..50

50-54 $.78

55-59 $1.46

60-64 $2.24

65-69 $3.78

70 and over $5.81

I do not think I will necessarily worry about inputing their birthdates into a column. I'll look up their bdays and make a rate column out by there name. So, my questions are the following:

1. Is there a way a formula can look up an employee with their corresponding rate, and know to multiply that rate by every $1,000 of wages earned (column H)?

Use a combination of Index and Match.

Create 2 named ranges
Ages = the values from 0-70 as below (each age is the lower age qualifying for that rate)
Rates = the values from 0.14-5.81 as below

The formula you need is
=INDEX(Rates,MATCH(B4,Ages,1),1)
The match is LESS than (the 1 after "Ages" inside the bracket so that if an exact match is not fount Excel returns the lower rate)
B4 is the cell containing the employees age


AGES RATES
0 0.14
30 0.18
40 0.34
45 0.5
50 0.78
55 1.46
60 2.24
65 3.78
70 5.81


See sheet3 in your workbook (attached)

And I would put in a column to calculate their ages immediately otherwise mistakes will occur

For Birthdays you could use this formula

Example:

If Cell B2 is 9/5/1983 place =DATEDIF(B2,$A$1,"Y") in C2. Where $A$1 is a fixed cell with =TODAY() in it.