how to pick from a range of table values

[jackoat]

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

 

[Anne Troy]

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/vlookup_formulas_in_microsoft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com

 

[Arvi Laanemets]

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.

 

[jackoat]

Hi Anne / Arvi

Thank you for your help. I have solved the problem by using many neste
if statements. Anyway, i will also try both your suggested soultions. B
the way, I have another problem. Let's say an employer pays a worker a
additional amount - a certain % of his salary EVERY SINGLE MONTH int
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 hav
received from his current age to a specified age. So, how much TOTA
additional amount would the employee have accumulated in each of th
three accounts (i.e. total A, total B, total C) from his current age a
47 years 0 month to the specified age at 57 years 3 months? How should
go about it? What functions can I use? The user would have to enter hi
date of birth and the future age in question.

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

Regards

Jac

 

[Anne Troy]

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

 

[Arvi Laanemets]

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(MATCH($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(MATCH($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(MATCH($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