function query - not sure which one possibly vlookup

[Lainyb]

I am trying to pick information on salary spinal column points within certain
grades. I want to look at the current scp in cell J2 and then get the next
scp for that grade. The grade information is in cell I2.

eg if I2 is grade 2 and J2 is SCP 11 then put the next SCP in cell T2 (13).
This would change until the top of the scale is reached. Anyone in Grade 2
cannot go above SCP 13. Lookup table for info below has been defined as SCP.

Grade SCP
1 1
1 3
1 5
2 7
2 9
2 11
2 13
3 15
3 17
3 19
3 21
4 23
4 25
4 27
4 29
5 31
5 33
5 35
5 37
6 39
6 41
6 43
6 45

Help with this would be greatly appreciated - really think I need a years
course on these lookups etc.

 

[Lars-Åke Aspelin]

I am trying to pick information on salary spinal column points within certain
grades. I want to look at the current scp in cell J2 and then get the next
scp for that grade. The grade information is in cell I2.

eg if I2 is grade 2 and J2 is SCP 11 then put the next SCP in cell T2 (13).
This would change until the top of the scale is reached. Anyone in Grade 2
cannot go above SCP 13. Lookup table for info below has been defined as SCP.

Grade SCP
1 1
1 3
1 5
2 7
2 9
2 11
2 13
3 15
3 17
3 19
3 21
4 23
4 25
4 27
4 29
5 31
5 33
5 35
5 37
6 39
6 41
6 43
6 45

Help with this would be greatly appreciated - really think I need a years
course on these lookups etc.

Assuming cells I2 and J2 always contain a valid combination of Grade
and SCP, and that the table of valid Grade and SCP are in cells
A2:B24, try the following formula in cell T2:

=IF(INDEX(A2:A25,MATCH(J2,B2:B24)+1)<>I2,J2,INDEX(B2:B24,MATCH(J2,B2:B24)+1))

This formula will give the next SCP for the grade in I2, but if the
SCP is the maximum SCP for that grade, the result will be that max
SCP.

Hope this helps / Lars-Åke

 

[T. Valko]

You don't say what should happen if J2 is already the max for the grade.

Try this array formula** :

=IF(J2=LOOKUP(2,1/(A2:A24=I2),B2:B24),"Already at max for the
grade",INDEX(B2:B24,MATCH(1,(A2:A24=I2)*(B2:B24=J2),0)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

mark

--
Biff
Microsoft Excel MVP

You don't say what should happen if J2 is already the max for the grade.

Try this array formula** :

=IF(J2=LOOKUP(2,1/(A2:A24=I2),B2:B24),"Already at max for the
grade",INDEX(B2:B24,MATCH(1,(A2:A24=I2)*(B2:B24=J2),0)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

 

[Lainyb]

Hi thanks for that - I should have said that when the max for the grade is
reached I2 should stay the same as J2.

I have tried your formula and it is returning #N/A - any idea of where I am
going wrong.

The lookup information for cell I2 (grade) is in B2:B60 therefore I
substituted your A2:24 with B2:B60 and the spinal point information to be
returned in cell T2 is in the lookup under C2:C60 so I substituted your
B2:B24 with C2:C60.

Thanks

 

[T. Valko]

when the max for the grade is
reached I2 should stay the same as J2.

OK, let's change the array formula** to this:

Grade refers to B2:B60
SCP refers to C2:C60

=IF(J2=LOOKUP(2,1/(Grade=I2),SCP),J2,INDEX(SCP,MATCH(1,(Grade=I2)*(SCP=J2),0)+1))

I have tried your formula and it is returning #N/A

Make sure you enter the formula as an array**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Lainyb]

Thanks very much for your help with this. It is now working.

Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!