IF Statement with AND function

[JohnZoom]

I have tracking workbook for calculating production bonus amounts. The following is the bonus breakdown.

Less Than 4000 = 0
4000 - 4999 = 40
5000 - 5999 = 50
6000 - 7999 = 60
8000 - 11999 = 80
>= 12000 = 120

My attempt has been the following but is erroring out.

=IF(AC2<4000,0,IF(AND(AC2>=4000,AC2<5000,40,IF(AND(AC2>=5000,AC2<6000,50,IF(AND(AC2>=6000,AC2<8000,60,IF(AND(AC2>=8000,AC2<12000,80,120)))))))))

Thanks.

[AZ-XL]

=vlookup(ac2,{0,0;4000,40;5000,50;6000,60;8000,80;12000,120},2,true)

[JohnZoom]

Thank you the vlookup worked perfect.

[xLJer]

Here is one solution:

Please Login or Register  to view this content.