Update result based on many fields condition in excel

Question

I currently have raw data as below

OID   Version   Type    RAW      RESULT         
6064    2842    154     failed
6064    2843    154     passed
6064    2844    154     failed
544448  837     154     failed
544448  838     154     failed
544448  839     154     passed
544448  840     154     failed
544448  841     154     failed

I'd like to have following result in excel

Expected

OID   Version   Type    RAW      RESULT         
6064    2842    154     failed   passed
6064    2843    154     passed   passed
6064    2844    154     failed   failed
544448  837     154     failed   passed
544448  838     154     failed   passed
544448  839     154     passed   passed
544448  840     154     failed   failed
544448  841     154     failed   failed

Rule

The same ID, the same type. If bigger version has result passed then mark all smaller versions' result passed (event though it's failed in RAW)

I've just tried Excel Lookup Formula with Two Conditions but no luck

Thanks,

Answer 1

This should work, although it might get a bit cumbersome dependant on how much data you are working with:

=IF(COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,">="&B2,$D$2:$D$9,"passed")>0,"passed","failed")

Breaking down the COUNTIFS function

$A$2:$A$9,A2         ' returns true if OID matching A2 is found
$B$2:$B$9,">="&B2    ' returns true if any version  found which is greater than or equal to B2
$D$2:$D$9,"passed"   ' returns true if any RAW result is "passed"

The COUNTIFS function counts the rows where all 3 of these conditions are true. If there are more than 0, the IF statement returns "passed", otherwise it returns "failed".