Return 1st & Last value in range, where criteria matches in a 12 month period

[JCTalk]

Hi Guys,

I'm racking my brain over this. On the face of it it feels like it should just be an index / match but horizontally. I just can't get it. I'm thinking it needs to include MAX and MIN? Any ninja's able to spare a helping hand?

I have a date range from Jan-15 to Dec-18 ($C$3:$AX$3). I have Pass / Fail for each month ($C$4:$AX$4). From a specific month, lets say this month July-17 ($B$1), within the prior 12 month period (so in this case Jul-16 to Jul-17), I'm trying to find the first Fail month and place it in ($A$4) and the last fail month and place it in ($B$4) .

Thank you for any help you can offer guys and gals.

(Example workbook attached with expected results of formula)

 

[bosco_yip]

Try,

1] First Month Failed, A3 formula :

=INDEX(OFFSET(B3,,MATCH(B1,C3:AX3,0),,-12),MATCH("Fail",OFFSET(B4,,MATCH(B1,C3:AX3,0),,-12),0))

2] Last Month failed, B3 formula :

=INDEX(OFFSET(B3,,MATCH(B1,C3:AX3,0),,-12),INDEX(MATCH(2,1/(OFFSET(B4,,MATCH(B1,C3:AX3,0),,-12)="Fail")),0))

Regards
Bosco

 

[JCTalk]

Try,

1] First Month Failed, A3 formula :

=INDEX(OFFSET(B3,,MATCH(B1,C3:AX3,0),,-12),MATCH("Fail",OFFSET(B4,,MATCH(B1,C3:AX3,0),,-12),0))

2] Last Month failed, B3 formula :

=INDEX(OFFSET(B3,,MATCH(B1,C3:AX3,0),,-12),INDEX(MATCH(2,1/(OFFSET(B4,,MATCH(B1,C3:AX3,0),,-12)="Fail")),0))

Regards
Bosco

Thats fantastic. Thank you Bosco. I knew it would be an index match but Offset has always baffled me. I really must try and start using it more to get used to it as I know it can be used in so many situations.

I've worked through the formulas and I can see whats going on here. Very efficient looking formula. I realised I actually needed a 13 month period so I tweaked that and it still works perfectly.

Many thanks again Bosco. Kudos to you, and kudos again!