How to compare a cell value to ranges in an array

[jvanhyfte]

Hello All,
I have always found answers to me XL questions here. Thanks for the great forum!
My question is this...

I have groups of people that I have calculated ages. Each of these ages are used to calculate an insurance rate factor and then multiplied by a value to get a cost. I have attached the data table that I am using...

Age Employee
Low High Rate
0 29 $0.49
30 34 $0.58
35 39 $0.72
40 44 $0.99
45 49 $1.55
50 54 $2.40
55 59 $4.10
60 64 $6.25
65 69 $11.39
70 100 $23.57

How would I put together either a VBA script or a formula that would take someone's age (say an employee is 36), and find the range this person falls within (35-39), and then calculate the rate ($0.72)? I have about 200 records on 4 sheets that I have to calculate these values, so I'm thinking a VBA script would work, but I can't figure how...

Thanks!

 

[Colin Legg]

LOOKUP() could work for you here.

eg.

=LOOKUP(36,A1:A10,C1:C10)

where A1:10 is the low age values and C1:C10 is the rate.

It assumes that the ages are sorted smallest to largest with no overlap/gaps in the age bands.

 

[jvanhyfte]

LOOKUP() could work for you here.

eg.

=LOOKUP(36,A1:A10,C1:C10)

where A1:10 is the low age values and C1:C10 is the rate.

It assumes that the ages are sorted smallest to largest with no overlap/gaps in the age bands.

That is so close to the solution, but I need a cell reference returned in order for me to do the additional math. Any suggestions?


P.S.
I knew I would get the help needed, but am blown away by the quick response. Thanks!

 

[Scott Huish]

Why do you need a cell reference returned when the LOOKUP formula will return the appropriate value?