Right to left conditional lookup

[Sthlm]

I'm working in regression model, which needs some tweaking. It needs to be able to exclude certain weeks which are classified as an outlier.

I've added an example file.

In rows 2 and 3 are my data which can go on indefinatly. And in row 18 the desired result, which is always 26 weeks.
Cell AB18 should look for the last week with a 'NO' value in row 3. Based on that value in AB18 is should look for the previous 25 weeks which were no outliers.

Any ideas on how I can accomplish this?

Thanks!

[Sadath31]

Hi

please find the attached...

[Sthlm]

Thanks Sadath, it works great!

It's a comepletely different approach to the problem. I tried working with dynamic named ranges and offset. This is a much simpler solution.