Match after specific value?

[JPGKIll3R]

Hello, mates!

I have a weird requirement in the job and haven't been able to figure it out by myself.
I;ve been given a weekly dataset (which needs to be in columns - per business requirements), similar to the table below:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]...[/TD]
[TD]W52[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Bad[/TD]
[TD]Bad[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]...[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Good[/TD]
[TD]Good[/TD]
[TD]Bad[/TD]
[TD]Good[/TD]
[TD]...[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]

What I'm being prompted to do, is identify the Week on which the status passes from "Bad" to "Good".
On the Product 1 example, the formula is quite simple =MATCH("Good",Range,0)
However, the formula is pulling Week1 for the Product 2 example, where it should be Week 4, as that is the first time the product goes "Good" after being "Bad".

any help?

 

[JoeMo]

Here's a UDF that can be used just like a native Excel function after you install it. See the example below (copy the formula in G2 down).

Excel Workbook
	A	B	C	D	E	F	G
1	Product	W1	W2	W3	W4		Transition Week
2	Product 1	Bad	Bad	Good	Good		3
3	Product 2	Good	Good	Bad	Good		4
4	Product 3	Good	Good	Good	Good		No Transition
Sheet2

Function TransitionWeek(R As Range) As Variant
Dim V As Variant, i As Long
V = R.Value
For i = 1 To UBound(V, 2)
    If i < UBound(V, 2) Then
        If V(1, i) = "Bad" And V(1, i + 1) = "Good" Then
            TransitionWeek = i + 1
            Exit Function
        End If
    Else
        TransitionWeek = "No Transition"
    End If
Next i
End Function

 

[jtakw]

Hi,

Formula option, change/adjust cell references/range as needed.

Let us know how you want to handle something like my sample Rows 4, 5, and 7:

Book1
	A	B	C	D	E	F	G
1	Product	W1	W2	W3	W4
2	Product 1	Bad	Bad	Good	Good		W3
3	Product 2	Good	Good	Bad	Good		W4
4	Product 3	Good	Good	Good	Good		Good
5	Product 4	Bad	Good	Good	Bad		Bad
6	Product 5	Good	Bad	Bad	Good		W4
7	Product 7	Bad	Bad	Bad	Bad		Bad
Sheet569

Cell Formulas
Range	Formula
G2	=IFERROR(IFNA(INDEX(A$1:E$1,,LOOKUP(2,1/SEARCH("Bad",B2:E2),COLUMN(B$1:E$1))+1),"Good"),"Bad")

Formula copied down.