If Statement with AND problem

[bpiroma]

Hi,

My problem here is that in my formula there is two different logical test that has the same condition.
What my formula needs to do is to give the sanction depending on the number of instance per offense.

Example:

Tardy:
1st Offense: Documented Coaching
2nd offense: Documented Coaching
3rd offense: First Written
4th Offense: Final Written
5th offense: 2 day Suspension
6th offense:Termination

Tardy:
1st Offense: Documented Coaching
2nd offense: Documented Coaching
3rd offense: First Written
4th Offense: Final Written
5th offense: 2 day Suspension
6th offense:Termination

AWOL :
1st Offense: Final Written
2nd offense: 2 day Suspension
3rd offense: Termination

Undertime:
1st Offense: 2 day Suspension
2nd Offense: Termination

here is the formula I am using right now: =IF(AND(K28="Tardy",L28<=2),"Documented Coaching",IF(AND(K28="Tardy",L28=3),"First Written",IF(AND(K28="Tardy",L28=4),"Final Written",IF(AND(K28="Tardy",L28=5),"2 day Suspension",IF(AND(K28="Tardy",L28>=6),"Termination",IF(AND(K28="Overbreak",L28<=2),"Documented Coaching",IF(AND(K28="Overbreak",L28=3),"First Written",IF(AND(K28="Overbreak",L28=4),"Final Written",IF(AND(K28="Overbreak",L28=5),"2 day Suspension",IF(AND(K28="Overbreak",L28>=6),"Termination",IF(AND(K28="Undertime",L28=1),"2 day Suspension",IF(AND(K28="Undertime",L28>=2),"Termination",IF(AND(K28="Absence Unplanned and Unapproved",L28=1),"First Written",IF(AND(K28="Absence Unplanned and Unapproved",L28=2),"Final Written",IF(AND(K28="Absence Unplanned and Unapproved",L28=3),"2 day Suspension",IF(AND(K28="Absence Unplanned and Unapproved",L28>=4),"Termination",IF(AND(K28="AWOL",L28=1),"Final Written",IF(AND(K28="AWOL",L28=2),"2 day Suspension",IF(AND(K28="Undertime",L28>=3),"Termination")))))))))))))))))))

It works for all offenses except for overbreak since it has the same condition with tardy.
Anyone who can help me sort this out?

Thanks in advance.

[twiggywales]

Stop set up a table

Then use index and match, nested ifs like that can be delt with a lot better, if I wasn't in a iPad I'd mock you,up a sheet fast but I can't

I think if you listed your offence along the top
The number down the side then the punishment in the matrix and index / match it it will solve your problem a lot more effiecntly.

[bpiroma]

Thanks for the reply.
I will try it then, hopefully I'd be able to make use of it since I am not familiar with it.

Just in case, I am attaching the worksheet that I am using.
The table on top is the tracker itself while the table below is the offense/instance matrix.

[vlady]

in G4

=INDEX($C$17:$H$21,MATCH(E4,$B$17:$B$21,0),F4) - fill down


=iferror(INDEX($C$17:$H$21,MATCH(E4,$B$17:$B$21,0),F4),"No offense found")

or

INDEX($C$17:$H$21,MATCH(E4,$B$17:$B$21,0),F4)&""

[bpiroma]

Coolness.. Super thanks vlady..