Excel Substract Values

Question

> STUDENT   SCORE   VALID   DIF
1   83  YES 0
1   77  YES -6
1   2   NO  -99
1   66  YES -11
1   100 YES 34
2   68  YES 0
2   98  NO  -99
2   61  YES -7
2   100 YES 39
2   64  YES -36
3   72  YES 0
3   83  YES 11
3   93  YES 10
3   52  YES -41

I have 'STUDENT' and 'SCORE' and 'VALID' but wish to create 'DIF' which equals to the DIFFERENCE between the scores ONLY when 'VALID' is YES like shown below. OBVIOUSLY the first DIF is 0 but then you take the difference from the top score to the next one and that is how DIF is calculated. But I can do this like C3-C2 but I need it separate for each STUDENT and ignoring the cells when VALID is NO

Answer 1

With XLOOKUP we can search from the bottom up to find the last that matches the needed criteria:

=IF(C2="No",-99,B2-XLOOKUP(1,($A$1:A1=A2)*($C$1:C1="YES"),$B$1:B1,B2,0,-1))