Testing a person's age to be within a range

[Guest]

Would like to use a vlookup.
Reference a cell for an employees age, then test that age to see what age
band it is within. Once it's determined, return a rate that is found in a
different column containing applicable rates for the age band. Not sure if I
need two columns to establish an age range or if I can do that within the
same cell (ie: col z = 25, col aa = 30 to establish an age range of 25-30 or
if I could do that in one cell with the text: 25-30)

Basically... If a person is age 25-29, return the rate of insurance.
Col A = Age Band (or perhpas split top and bottom of range into two
different col's?)
Col B = Rate of insurance for that age band

Many thanks for any help.

 

[Roger Govier]

Hi

Set your table up as follows
0 1
25 2
29 3
34 4
etc. where the 1,2,3,4 would be changed to represent your rates.

If these are in Z1:AA4 then your formula would be
=VLOOKUP(A1,$Z$1:$AA$4,2)

 

[Guest]

I don't think I was clear....

I'm trying to reference a person's age somewhere else on the worksheet,
check it against a chart and then return the correct rate for that age based
on the chart's values.

The formula would follow this thinking:

-Reference the employee's age cell
-Test the age of the employee living in this row against the chart.
-Return the rate for the age band.

Ages Rate
0-24 0.19
25-29 0.24
30-34 0.37
35-39 0.54
40-44 0.73
45-49 1.07
50-54 1.48
55-59 1.87
60-64 1.84
65-69 2.16
70-99 2.71

Hope that's making sense.

 

[Roger Govier]

Yes, you were clear.
Just enter the first values of your age range in one column, and the
rates in the adjacent column
0 0.19
25 0.24
29 0.37
etc.

I assumed this data was in columns Z and AA as that's what you
mentioned.
Seeing the full table now, the range would obviously be $Z$1:$AA$11 but
this could be in any pair of adjacent columns to suit.
Again, I assumed the person's age was in A1 and the formula entered in
B1 would return the appropriate rate for that age
=IF(A1="","",VLOOKUP(A1,$Z$1:$AA$11,2))
copy down as required
I have added an If statement so that if there is no age entered in Colum
A you will not get a #N/A error.

Give it a try.

 

[Ragdyer]

I think he's looking for a single formula to *both* lookup the age, then
lookup the percent of that age from the age band.

With employee names in A1 to A25,
Ages in B1 to B25,

Employee name to find entered into C1

Age - Percent datalist in Z1 to AA11, with this format:

Z AA
0 0.19
25 0.24
30 0.37
35 0.54
40 0.73
45 1.07
50 1.48
55 1.87
60 1.84
65 2.16
70 2.71

Try a formula something like this:

=LOOKUP(VLOOKUP(C1,A1:B25,2,0),Z1:AA11)

 

[Guest]

OK- we're on the same page. You're telling me how to do a simple vlookup but
I need some more. Your suggestion will only work if I have 1 age and 1 rate
in each of the columns vlookup is looking in. I'm trying to avoid entering
every possible age/corresponding rate between age 24 and 71.

I guess I'm struggling with how do I get the rate for a 38 yr old when I
have this info to work with:
Age Rate
35-39 0.54

Thanks for your patience.

 

[Guest]

You're on the right track...

I'm trying to use one formula that looks at a cell (this is where it finds
the employees age) but then checks a chart somewhere else on the worksheet to
see if that employees age falls within an age range, then returns the rate
that is in the column next to the age range. Here's what I wrote back to
someone else:

OK- we're on the same page. You're telling me how to do a simple vlookup but
I need some more. Your suggestion will only work if I have 1 age and 1 rate
in each of the columns vlookup is looking in. I'm trying to avoid entering
every possible age/corresponding rate between age 24 and 71.

I guess I'm struggling with how do I get the rate for a 38 yr old when I
have this info to work with:
Age Rate
35-39 0.54

Thanks for your patience.

 

[Roger Govier]

As I said - try it.

You do not have to have every age entered.
By not having the 4th parameter within Vlookup ( 0 or False) the default
is True (or 1).
That being so, Vlookup will not look for an exact match, but will give
the highest value which is lower than the value being looked up, hence
38 would fall back to the 35 range which would use a value of 0.54

 

[Ragdyer]

That's *exactly* what you have with *both* Roger's and my formulas.

I just added a portion to lookup the age when a name was given.

 

[Guest]

I found it in someone else's chat.
The answer was a simple vlookup but instead of trying to use a range, simply
use the minimum value for the beginning of the range and the formula works.

 

[Roger Govier]

Exactly what RD and I told you.
Happy New Year.