If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
function query - not sure which one possibly vlookup
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 |
#2
|
|||
|
|||
function query - not sure which one possibly vlookup
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 |
#3
|
|||
|
|||
function query - not sure which one possibly vlookup
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 |
#4
|
|||
|
|||
function query - not sure which one possibly vlookup
mark
-- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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 |
#5
|
|||
|
|||
function query - not sure which one possibly vlookup
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 |
#6
|
|||
|
|||
function query - not sure which one possibly vlookup
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 |
#7
|
|||
|
|||
function query - not sure which one possibly vlookup
Thanks very much for your help with this. It is now working.
Thanks again. -- Lainyb "T. Valko" wrote: 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 |
#8
|
|||
|
|||
function query - not sure which one possibly vlookup
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Lainyb" wrote: Thanks very much for your help with this. It is now working. Thanks again. -- Lainyb "T. Valko" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|