On Sat, 7 Nov 2009 11:48:01 -0800, 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.


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