Running Tally Traffic Light System - Find Previous Values Upwards In A Column

I'm trying to work out to most efficient way of making a quality monitoring system which uses traffic lights to show problem products.

The traffic light system need to obey the following "rules".

Each product is tested once daily When a product fails once it moves to Amber, When a product Fails twice in a row it moves to RedOne pass when a product is in Amber Status, will reset it to green.If a Product is in Red status, it much have 3 consecutive passes to reset to Green.

I have tried to use array formulae to look up a column for previous values in the past for this sort of thing, but with 100000+ rows it runs far too slowly.

Banana Problem?
Product
Result
Traffic Light (0=green, 1=amber, 2=red)

[Code].....