I need to check that all instances of the value in the blue table Index column have a corresponding value of YES, and report this data into the green table.
My attempted solution
I think I have a solution... but surely there is something better than this janky method!
={SUM((Table1[INDEX]=[@INDEX])*(Table1[VALUE]="YES"))=COUNTIF(Table1[INDEX],Table2[@])}
Try this formula under your "PASS" column in green table:
=IF((COUNTIF(A:A,D2))=COUNTIFS(B:B,"YES",A:A,D2),"YES","NO")
Note:
Change the column value regarding your column (based on your excel). The above formula is written for your table considering your BLUE table is Column A, B -- empty column C -- GREEN table is Column D, E
You could also just count "No" rather than "Yes", which simplifies the formula:
=IF(COUNTIFS(A:A,D1,B:B,"NO")>0,"NO","YES")