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.