Try...

=INDEX(D2:D8,LARGE(IF(B2:B8="Bob",IF(C2:C8=1,ROW(D 2:D8)-ROW(D2)+1)),2))

....confirmed with CONTROL+SHIFT+ENTER .

Hope this helps!

In article ,
ceemo wrote:

ColA ColB ColC ColD
Date Name complete% Grade A
28/1/6 bob 100% A
31/1/6 bob 100% G
1/2/6 bob 100% C
1/2/6 pete 100% E
2/2/6 bob 65% C
2/2/6 steff 89% C
3/2/6 bob 100% B

I have the below formula to get the second to last date that Bob got
100% but i would like to get the grade (C) that Bob got on th second to
last time he got 100%

=LARGE(IF(B2:B8="Bob", IF(C2:C8=1, A2:A8)),2)

Can u help ?