formula to return status of a PO w/ multiple rows and multiple status

[rando123]

Hello

I need assistance with coming up with a formula for spreadsheet 2 column 2.
I have a list of all PO in spreadsheet 1 and need to Pass or Fail based on the status from spreadsheet 1. If it contains a no or maybe then fail. this is a s implied version.



[TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl63, width: 134, colspan: 2"]Spreadsheet 1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]123290[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]9889[/TD]
[TD]Maybe[/TD]
[/TR]
[TR]
[TD]9889[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]9889[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]0984035[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]0984035[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0984035[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]0984035[/TD]
[TD]Maybe[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet 2 (separate sheet): Results in Column 2 is what I NEED but can't figure out
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]PO[/TD]
[TD="align: center"]Look Up to Spreadsheet 1 and Return a Fail if contains No OR Maybe, otherwise Pass[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Pass[/TD]
[/TR]
[TR]
[TD]123290[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]9889[/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD]0984035[/TD]
[TD]Fail[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

Sheet2 B2 copied down
=IF(COUNTIF(Sheet1!A:A,A2),IF(SUM(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,{"No";"Maybe"})),"Fail","Pass"),"Not Found")

Hope this helps

M.

 

[rando123]

Hi Marcelo

Thanks so much, this does appear to work just as needed!!

Maybe...

Sheet2 B2 copied down
=IF(COUNTIF(Sheet1!A:A,A2),IF(SUM(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,{"No";"Maybe"})),"Fail","Pass"),"Not Found")

Hope this helps

M.