how to pick from a range of table values
 

Hi

I am trying to use vlookup to pick up a value corresponding to a
particular age group. However, as far as i know, Vlookup only returns a
value based on a specific input value, rather than a range of values.

For example, if the table contains the bonus rates based on age group.

e.g. age 35 and below, rate = 5%
age 36 - 45, rate = 6%
age 46 - 55, rate = 8% etc.

I want a particular cell to return the corresponding bonus rate from
the table when the user enters his age in another cell. e.g. for age
40, it gives 6%

What can I do?

Looking forward to your help out there. Thanks!!

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile: http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055

Hi, Jack. No, vlookup WILL let you use a range. Just create your table like
this:
35 5%
45 6%
55 8%
etc....
Do NOT use the 4th argument for your vlookup (which is normally done when
you are looking up text values). Just =vlookup(A1,mylookup,2)
See:
http://www.officearticles.com/excel/...soft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com


"jackoat" wrote in
message ...

Hi

I am trying to use vlookup to pick up a value corresponding to a
particular age group. However, as far as i know, Vlookup only returns a
value based on a specific input value, rather than a range of values.

For example, if the table contains the bonus rates based on age group.

e.g. age 35 and below, rate = 5%
age 36 - 45, rate = 6%
age 46 - 55, rate = 8% etc.

I want a particular cell to return the corresponding bonus rate from
the table when the user enters his age in another cell. e.g. for age
40, it gives 6%

What can I do?

Looking forward to your help out there. Thanks!!

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055

Hi

Like this:
=CHOOSE(MATCH(A1,{0;36;46;56},1),0.05,0.06,0.08,0. 10)
You can expand this up to 29 values to choose between.

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"jackoat" wrote in
message ...

Hi

I am trying to use vlookup to pick up a value corresponding to a
particular age group. However, as far as i know, Vlookup only returns a
value based on a specific input value, rather than a range of values.

For example, if the table contains the bonus rates based on age group.

e.g. age 35 and below, rate = 5%
age 36 - 45, rate = 6%
age 46 - 55, rate = 8% etc.

I want a particular cell to return the corresponding bonus rate from
the table when the user enters his age in another cell. e.g. for age
40, it gives 6%

What can I do?

Looking forward to your help out there. Thanks!!

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055

Hi Anne / Arvi

Thank you for your help. I have solved the problem by using many nested
if statements. Anyway, i will also try both your suggested soultions. By
the way, I have another problem. Let's say an employer pays a worker an
additional amount - a certain % of his salary EVERY SINGLE MONTH into
three different accounts, A, B and C according to his age. For example,


for age 35-45, rate A = 10%, rate B= 8% and rate C=6%
age 45 - 55, rate A = 9%, rate B= 7%, rate C=5%
age 55 - 65, A= 7%, B = 5 %, C= 4%
age 65 - 75 A= 6%, B=4%, C= 3%

I am trying to calculate the total amount that an employee would have
received from his current age to a specified age. So, how much TOTAL
additional amount would the employee have accumulated in each of the
three accounts (i.e. total A, total B, total C) from his current age at
47 years 0 month to the specified age at 57 years 3 months? How should I
go about it? What functions can I use? The user would have to enter his
date of birth and the future age in question.

Hope you are able to help me this time as well. Thanks!!

Regards

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile: http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055

I'm sorry, Jack. Those kinds of formulas always baffle me, so while I can't
answer, I'm definitely watching for a resolution right along with you.
*******************
~Anne Troy

www.OfficeArticles.com


"jackoat" wrote in
message ...

Hi Anne / Arvi

Thank you for your help. I have solved the problem by using many nested
if statements. Anyway, i will also try both your suggested soultions. By
the way, I have another problem. Let's say an employer pays a worker an
additional amount - a certain % of his salary EVERY SINGLE MONTH into
three different accounts, A, B and C according to his age. For example,


for age 35-45, rate A = 10%, rate B= 8% and rate C=6%
age 45 - 55, rate A = 9%, rate B= 7%, rate C=5%
age 55 - 65, A= 7%, B = 5 %, C= 4%
age 65 - 75 A= 6%, B=4%, C= 3%

I am trying to calculate the total amount that an employee would have
received from his current age to a specified age. So, how much TOTAL
additional amount would the employee have accumulated in each of the
three accounts (i.e. total A, total B, total C) from his current age at
47 years 0 month to the specified age at 57 years 3 months? How should I
go about it? What functions can I use? The user would have to enter his
date of birth and the future age in question.

Hope you are able to help me this time as well. Thanks!!

Regards

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055

Hi

I would use a helper sheet. P.e. on sheet Mainboard the user enters
BirthDate, FutureAge and Salary (my advice is define the sells with entered
values as names). Based on BirthDate, CurrentAge is calculated. It will be
easier with further calculations, when FutureAge and CurrentAge are in
format yy.mm - in this case you can calculate CurrentAge as:
=DATEDIF(BirthDate,TODAY(),"Y")+DATEDIF(BirthDate, TODAY(),"YM")/100

On sheet Matrix you have a table
Age, AmountA, AmountB, AmountC

Column Age contains ages in format yy.mm from 35.00 to 75.00, i.e.
35.00
35.01
35.02
....
35.11
35.12
36.00
36.01
etc.

Into cell in row 2 of coumn AmountA enter formula like this
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.1,0.09,0.07,0.06,0)*Salary, 0)
For AmountB
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.08,0.07,0.05,0.04,0)*Salary ,0)
For AmountC
=IF(AND($A2=CurrentAge,$A2<=FutureAge),CHOOSE(MAT CH($A$2,{0;34.12;44.12;54.
12;64.12;74.12},1),0,0.06,0.05,0.04,0.03,0)*Salary ,0)
, and copy formulas down for entire table.

Define named values
AmountA=SUM(Matrix!$B:$B)
AmountB=SUM(Matrix!$C:$C)
AmountC=SUM(Matrix!$D:$D)

On sheet Mainboard, enter formular returning amounts into some cells on your
choice. You can hide the sheet Matrix, when you want.
It's done!


Arvi Laanemets


"jackoat" wrote in
message ...

Hi Anne / Arvi

Thank you for your help. I have solved the problem by using many nested
if statements. Anyway, i will also try both your suggested soultions. By
the way, I have another problem. Let's say an employer pays a worker an
additional amount - a certain % of his salary EVERY SINGLE MONTH into
three different accounts, A, B and C according to his age. For example,


for age 35-45, rate A = 10%, rate B= 8% and rate C=6%
age 45 - 55, rate A = 9%, rate B= 7%, rate C=5%
age 55 - 65, A= 7%, B = 5 %, C= 4%
age 65 - 75 A= 6%, B=4%, C= 3%

I am trying to calculate the total amount that an employee would have
received from his current age to a specified age. So, how much TOTAL
additional amount would the employee have accumulated in each of the
three accounts (i.e. total A, total B, total C) from his current age at
47 years 0 month to the specified age at 57 years 3 months? How should I
go about it? What functions can I use? The user would have to enter his
date of birth and the future age in question.

Hope you are able to help me this time as well. Thanks!!

Regards

Jack


--
jackoat
------------------------------------------------------------------------
jackoat's Profile:
http://www.excelforum.com/member.php...o&userid=25800
View this thread: http://www.excelforum.com/showthread...hreadid=392055