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.

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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" wrote:

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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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.


"Ragdyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"MH" wrote in message
...
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.


"Ragdyer" wrote:

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)

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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.

"MH" wrote:

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.


"Ragdyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.

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

--
Regards

Roger Govier


"MH" wrote in message
...
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.

"MH" wrote:

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.


"Ragdyer" wrote:

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"MH" wrote in message
...
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" wrote:

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)

--
Regards

Roger Govier


"MH" wrote in message
...
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.