IF Statement - multiple criteria - won't quite work

This formula started out much smaller, however there are 9 levels within the comp plans. This formula work fine, when I had it at 5 levels, but I added the additional components, and I just cannot figure out where my mistake is. Before this formula, everything was being done manually, and I am trying to automate as many of the calculations as possible. The issue before was that EACH month the formulas were being updated with the new criteria for the levels, now I have it all linked to tables (on the Comp Plans tab). I have been fighting... and losing with this formula for at least an hour know. I know it is something simple, I just cannot seem to SEE the problem. What am I doing wrong? I am Using Excel 2010 also.


=IF(K2=0,"-",IF(AND($K2>='Comp Plans'!$C$115,$K2<='Comp Plans'!$E$115),'Comp Plans'!$B$115,IF(AND($K2>='Comp Plans'!$C$116,$K2<='Comp Plans'!$E$116),'Comp Plans'!$B$116,IF(AND($K2>='Comp Plans'!$C$117,$K2<='Comp Plans'!$E$117),'Comp Plans'!$B$117,IF(AND($K2>='Comp Plans'!$C$118,$K2<='Comp Plans'!$E$118),'Comp Plans'!$B$118,IF(AND($K2>='Comp Plans'!$C$119,$K2<='Comp Plans'!$E$119),'Comp Plans'!$B$119,IF(AND($K2>='Comp Plans'!$C$120,$K2<='Comp Plans'!$E$120),'Comp Plans'!$B$120,IF(AND($K2>='Comp Plans'!$C$121,$K2<='Comp Plans'!$E$121,'Comp Plans'!$B$121,IF(AND($K2>='Comp Plans'!$C$122,$K2<='Comp Plans'!$E$122),'Comp Plans'!$B$122,IF(AND($K2>='Comp Plans'!$C$123),'Comp Plans'!$B$123,"No Bonus"))))))))

Deleted...

Looks like you got a table of ranges in C and E with a lookup in column B
You may need to modify it for the "No Bonus" result

Replace your formula with this

=INDEX('Comp Plans'!B$115:B$123,MATCH(K2,'Comp Plans'!C$115:C$123,1),1)

Ya'll rock! Obviously I was "overcomplicating" things! Thanks for the claification! I used the original formula you provided me with and it worked perfectly!

=INDEX('Comp Plans'!B$115:B$123,MATCH(K2,'Comp Plans'!C$115:C$123,1),1)

I then made some slight revisions to account for the "NO Bonus" aspect, and this is what I came up with... which works perfect. Thanks so very much!!!!!

=IF(K2="-","-",IF($K2<'Comp Plans'!$C$115,"No Bonus",INDEX('Comp Plans'!$B$115:$B$123,MATCH($K2,'Comp Plans'!$C$115:$C$123,1),1)))