Vlookup or if function

[sumonrezadu]

My Boss set a "criteria" for incentive calculation which is given in H7:U33. He will change the no. of increment given in that area whenever he wants. My job is to give a formula in "D" Column so that it acquire the appropriate value based on criteria. I tried but my formula will be long enough. Let me clarify the criteria

Incentive depend of designation, job length and grade. For example if designation is General manager and his job length is "8" years and he falls in grade B then in "D3", value will be "6" and so on.

Can anyone help plz?

[benishiryo]

hi sumonrezadu, i did for just Grade B. i didnt see other grades other than C.

=INDEX($J$8:$O$33,MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1))

let me know if you have them

[martindwilson]

change the values in j7:o7 to 0 5 10 15 20 25
then you can use
=INDEX($J$8:$O$33,MATCH(A3,$H$8:$H$33,0),IF(C3="b",MATCH(B3,$J$7:$O$7,1),MATCH(B3,$J$7:$O$7,1)+6))

[sumonrezadu]

Thanks a lot. Actually I need only grade B and C and no other grade. What is the solution if it falls in grade "C"? My problem is solved for grade B but how can I incorporate grade C also in this formula?
Pl help

[sumonrezadu]

Thanks to martindwilson, can it be zero in D3 instead of 6 if C3=0 (i.e if general manager falls in grade C) because Q8=0.
Ty

[benishiryo]

then it can be:
=INDEX(($J$8:$O$33,$P$8:$U$33),MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1),IF($C3="B",1,2))

[martindwilson]

the formula i gave you does b and c

[sumonrezadu]

Wow that's gr8. I'm really grateful. My prob is solved. But for my learning I cant understand curly bracket "{}". When we use that would u pl make me sense. Thx a lot to benishiryo again

[martindwilson]

{0,5,10,15,20,25} is the same as using values in a range eg $J$7:$O$7 .

[sumonrezadu]

Yes I understand. Thanks

[sumonrezadu]

One problem found. there should be 0 value against D or E grade but it is showing value. Value should come against B or C grade only. I cant understand "IF($C3="B",1,2)" this part. Is it if C3=C or D or E, then value would be 2 because it would be 1 only if c3=B.

then it can be:
=INDEX(($J$8:$O$33,$P$8:$U$33),MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1),IF($C3="B",1,2))

[martindwilson]

=if(OR(c3={"e","d"}),0,iNDEX(($J$8:$O$33,$P$8:$U$33),MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1),IF($C3="B",1,2)))

in the posted solution

=INDEX(($J$8:$O$33,$P$8:$U$33),MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1),IF($C3="B",1,2))
this uses the
INDEX(reference,row_num,column_num,area_num) method of using index
$J$8:$O$33,$P$8:$U$33 are the 2 areas
IF($C3="B",1,2) chooses area 1 or 2 so if it isnt "b" it chooses area 2 and returns a value

[sumonrezadu]

Yes martindwilson, you are right. Actually my problem is solved by you and by benishiryo also. But my Boss is asking me now to include grade "D" also. Duration will be same as grade "B" & "C". Is there any way? Pl held if anyone can

the formula i gave you does b and c

[martindwilson]

assuming values for d go in range $V$7:$Z$33 (note there was a typo in =IF(b3="e" should have been =IF(c3="e")
=IF(C3="e",0,INDEX(($J$8:$O$33,$P$8:$U$33,$V$7:$Z$33),MATCH($A3,$H$8:$H$33,0),MATCH($B3,{0,5,10,15,20,25},1),CODE(UPPER(C3))-65))