What function(s) to use to pick up a value based on two variables?

  • Thread starter Thread starter Penny
  • Start date Start date
P

Penny

I have tried various avenues to resolve this problem, to no avail, so I'm
hoping that someone on this list may be able to provide assistance.

I need to create a formula that picks up a percentage based on two
variables, age and years of service. I have tried various functions,
including LOOKUP, VLOOKUP, INDEX, AND, OFFSET and MATCH functions, I have
tried nesting formulae within VLOOKUP and HLOOKUP functions, but I cannot
get it to work. I have raised this with commercial Excel trainers, and they
have been unable to give me an answer.

Below is a sample table of what I mean, and an example of what the result
should be based on specified information. What I need to be able to do is
calculate a pension. The pension is a percentage of the person's final
salary. That percentage is determined by their age and their years of
service.


Any help that can be provide would be greatly appreciated.

Yours sincerely,


Penny

Canberra






Years service















20
21
22
23
24
25


50
1.00%
2.00%
3.00%
4.00%
5.00%
6.00%

Age
51
2.00%
3.00%
4.00%
5.00%
6.00%
7.00%


52
3.00%
4.00%
5.00%
6.00%
7.00%
8.00%


53
4.00%
5.00%
6.00%
7.00%
8.00%
9.00%


54
5.00%
6.00%
7.00%
8.00%
9.00%
10.00%


55
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%




Pension for someone aged 52 with 22 years service and final salary
$10,000:$10,000 * 5% = $500
 
One way of doing this

Age
50 51 52 53 54 55
20 1.00% 2.00% 3.00% 4.00% 5.00% 6.00%
21 2.00% 3.00% 4.00% 5.00% 6.00% 7.00%
22 3.00% 4.00% 5.00% 6.00% 7.00% 8.00%
23 4.00% 5.00% 6.00% 7.00% 8.00% 9.00%
24 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%
25 6.00% 7.00% 8.00% 9.00% 10.00% 11.00%

Then A20= number of years service
B20= Age
C20= VLOOKUP(A20,A12:G17,(B20-48),0) where A12:G17 is th
lookup table above

Hope this helps

Mik
 
I have tried various avenues to resolve this problem, to no avail, so I'm
hoping that someone on this list may be able to provide assistance.

I need to create a formula that picks up a percentage based on two
variables, age and years of service. I have tried various functions,
including LOOKUP, VLOOKUP, INDEX, AND, OFFSET and MATCH functions, I have
tried nesting formulae within VLOOKUP and HLOOKUP functions, but I cannot
get it to work. I have raised this with commercial Excel trainers, and they
have been unable to give me an answer.

Below is a sample table of what I mean, and an example of what the result
should be based on specified information. What I need to be able to do is
calculate a pension. The pension is a percentage of the person's final
salary. That percentage is determined by their age and their years of
service.


Any help that can be provide would be greatly appreciated.

Here's one way.
Set up your table as follows:

A B C D E F G
1 50 51 52 53 54 55
2 20 1.00% 2.00% 3.00% 4.00% 5.00% 6.00%
3 21 2.00% 3.00% 4.00% 5.00% 6.00% 7.00%
4 22 3.00% 4.00% 5.00% 6.00% 7.00% 8.00%
5 23 4.00% 5.00% 6.00% 7.00% 8.00% 9.00%
6 24 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%
7 25 6.00% 7.00% 8.00% 9.00% 10.00% 11.00%


Then the formula:

=Final_Salary*HLOOKUP(Age,B1:G7,1+MATCH(YearsService,A2:A7))

will give you the result you specify.

If either Age or YearsService are less than what is in the table, you will get
an #NA! error.



--ron
 
...
...
Set up your table as follows:

A B C D E F G
1 50 51 52 53 54 55
2 20 1.00% 2.00% 3.00% 4.00% 5.00% 6.00%
3 21 2.00% 3.00% 4.00% 5.00% 6.00% 7.00%
4 22 3.00% 4.00% 5.00% 6.00% 7.00% 8.00%
5 23 4.00% 5.00% 6.00% 7.00% 8.00% 9.00%
6 24 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%
7 25 6.00% 7.00% 8.00% 9.00% 10.00% 11.00%


Then the formula:

=Final_Salary*HLOOKUP(Age,B1:G7,1+MATCH(YearsService,A2:A7))

will give you the result you specify.

If this table is what's wanted, there's no need for it.

=Final_Salary*(MAX(0,MIN(25,YearsService)-19)
+MAX(0,MIN(55,Age)-49))/100

would give the desired result, and there'd never be any #N/A errors.
 
If this table is what's wanted, there's no need for it.

=Final_Salary*(MAX(0,MIN(25,YearsService)-19)
+MAX(0,MIN(55,Age)-49))/100


That's true. But would not the table be more flexible, in the event of future
changes?

Also, while easy to remove the #NA! error, it may serve a purpose for detecting
out of range entries, depending on the wishes of the OP. Of course, there are
other methods of detecting out of range entries.




--ron
 
Hi Ron,

Thank you for taking the time to answer my query.

I've tried it out, and it works a treat.

Thanks,

Penny
 
Hi Ron,

Thank you for taking the time to answer my query.

I've tried it out, and it works a treat.

Thanks,


You're welcome. Thanks for the feedback.


--ron
 
Back
Top