Making a list of possible botanic families based on rules

Hi

I' have a problem with this small farm project i'm working on. Dont know how to solve this so I'm posting in general section.

So I have cells in Column A where I have list of years (1-5). Each year can have up to max 3 plant possible to sowing in single year. In column C i have index/match formula which show me which botanical families bellong to each plant

What I would like to do ? If I select two plant in cell B2 Cabbage (belongs to Brassicaceae) and in cell B3 Carrot (Belongs to Apiaceae) to sow in 1st year
I would like to have list of botanical families which are possible to sow in 2nd year. And after 2nd year was selected list of possible botanic families to sow in 3rd year and so on till 5th year.



I made such list. It's in row 14 for second year. Row 15 is for 3'rd year and so on.

But my list does not follow the rules for crop rotation. For example the rule for Cabbage is (in 3 years, no more than 1 times Brassicaceae) and Carrot (in 3 years, no more than 2 times Apiaceae). So this basically means that in year 2 and 3 i should not have none of the plant from Brassicaceae family on this list. But from Apiaceae i should be allowed to choose in 3rd year and but not in 2nd year.



Each botanical family has own rule, but among all 8 families there is only 3 types of this rules . Detail list of rules is at cell (f2:m2) see attachment


- in 3 years, no more than 1 times
- in 3 years, no more than 2 times (but must not follow each other)
- in 5 years, no more than 1 times

excel file is in attachment



How to solve this ?

Thank you

File ''Test 2'' is what I was thinking. Now I need to look more into details and understand how this works

It takes me a while at the very first moment to understand everything. But now its all clear

Thank you