finding the rightmost zero

[Weaver]

On a sheet, Columns d - j represent financial years, the headers being in row 4

From row 5 downwards each column shows number of failures for the given period, with one row per asset.

The task is to determine the most recent period with a zero result and show in column N

I used the following array formula which works but nobody else in our organisation really understands array formulas (or anything beyond a sum() for that matter) so would like to replace it with something that doesn't use arrays. We rules out match (data isn't sorted) and hlookup (finds the first zero, no good if there's more than 1)

Any ideas?

=IF(MIN(D5:K5)> 0,"No Fail",OFFSET($A$4,0,MAX((D5:K5=0)*COLUMN(D5:K5))-1,1,1))

 

[RoryA]

How about:
=IF(MIN(D5:K5)> 0,"No Fail",LOOKUP(2,1/(D5:K5=0),$D$4:$K$4))

 

[Weaver]

That's great, thanks.

Now that I think of it, I remember seeing something along these lines on here a year or so ago (i.e using the lookup() that forces a division by zero.)