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

[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

 

[Freemini]

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

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

...
...

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.

 

[Ron Rosenfeld]

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

 

[Penny]

Hi Ron,

Thank you for taking the time to answer my query.

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

Thanks,

Penny

 

[Ron Rosenfeld]

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