Macro to determine values

I'm using the following formula:
=IF(ISNUMBER(MATCH(1,IF($A$2:$A$500=A322,IF($L$2:$L$500="Failed",1)),0)),"Failed",IF(ISNUMBER(MATCH(1,IF($A$2:$A$500=A322,IF($L$2:$L$500="Not Completed",1)),0)),"Not Completed","Passed"))

However, I want to incorporate a couple more things into the formula and can't figure it out. I want the following to happen:
1) If anything is Failed the result should be Failed
2) If everything is Passed the result should be Passed
3) If it's No Run and Passed the result should be Not Completed
4) If everything is No Run the result should be No Run

Those are the scenarios that the formula should cover. Any help is greatly appreciated.

Thanks in advance.