formula to select sample

[cshenoy]

I need a formula to select a sample. I have 2 main criteria. If either is yes, then it's a candidate. The other criteria are: I need 2 years of data before and after the year the criterion is met. If the criterion is met again before the end of the 5 yr period, it's not in the sample (non-overlapping periods)

In the example, attached I have one company that qualifies in 4 different years, but only two non-overlapping periods. The second company doesn't meet any criteria.

I hope this is clear enough. Thanks for your help!

[HansV]

I think the attached version does what you want. I needed an auxiliary column.

example.xlsx

[cshenoy]

Thanks Hans!

I'd still be working on this. It's a big help! I had gotten close with 4 columns, but still wasn't there yet.