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)*(S CP=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.


--
Biff
Microsoft Excel MVP


"Lainyb" wrote:

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
--
Lainyb


"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Lainyb" wrote:

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.

--
Lainyb