Nested if statement help

[acmumph]

So I'm trying to assign a class to an animal for a livestock show based on animals date of birth. I am able to get a single IF statement to work, but not able to get the conditions for all the possible classes. This is the statement I have that works:
=IF(AND(D2>Classes!$B$3,D2<Classes!$C$3),2)

What I need is:
=IF(AND(D2>Classes!$B$2,D2<Classes!$C$2),1)
=IF(AND(D2>Classes!$B$3,D2<Classes!$C$3),2)
=IF(AND(D2>Classes!$B$4,D2<Classes!$C$4),3)
=IF(AND(D2>Classes!$B$5,D2<Classes!$C$5),4)
=IF(AND(D2>Classes!$B$6,D2<Classes!$C$6),5)
=IF(AND(D2>Classes!$B$7,D2<Classes!$C$7),6)
=IF(AND(D2>Classes!$B$8,D2<Classes!$C$8),7)

So if I enter a date it will automatically populate the Class field as Class 1 - Class 7.

Thanks in advance,
Aaron

[Busclas]

Still a novice at most excel things, but without seeing your actual workbook to know if it works and what references what, you could try this. No guarantees.

=IF(AND(D2>Classes!$B$2,D2<Classes!$C$2),1,IF(AND(D2>Classes!$B$3,D2<Classes!$C$3),2,IF(AND(D2>Classes!$B$4,D2<Classes!$C$4),4,IF(AND(D2>Classes!$B$5,D2<Classes!$C$5),4,IF(AND(D2>Classes!$B$6,D2<Classes!$C$6),5,IF(AND(D2>Classes!$B$7,D2<Classes!$C$7),6,IF(AND(D2>Classes!$B$8,D2<Classes!$C$8),7,"No_Class")))))))

[FDibbins]

Hi and welcome to the forum

2 suggestions come to mind....
1. string the formulas together, something like,,,
=IF(AND(D2>Classes!$B$2,D2<Classes!$C$2),1,IF(AND(D2>Classes!$B$3,D2<Classes!$C$3),2),IF(AND(D2>Classes!$B$4,D2<Classes!$C$4),3)….)

OR
2. leave them like that - put them somewhere out of the way, and then use =max() to return the value.

[Kalithro]

Yes an example worksheet showing the dates and some expected results would help.

[acmumph]

showbreaker.xlsx

Worked like a champ...I tried something similar but could never seem to get it to work....Thanks much..Attached is workbook with your fix..

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Kalithro]

Can try this;

I just learned this one... took me a bit to get it right. If you want to change Class from numbers, to anything... Say Fat Cow, Skinny Cow, Young Cow, Old Cow... just change the data in column D.

=LOOKUP(2,1/--((B2:B8<E2)--(E2>C2:C8)),D2:D8)