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.

Spreadsheet 1

<tbody>
</tbody>

PO	Status
1234	Yes
1234	Yes
123290	No
9889	Maybe
9889	Yes
9889	Yes
0984035	Yes
0984035	No
0984035	No
0984035	Maybe

<tbody>
</tbody>

Spreadsheet 2 (separate sheet): Results in Column 2 is what I NEED but can't figure out

PO	Look Up to Spreadsheet 1 and Return a Fail if contains No OR Maybe, otherwise Pass
1234	Pass
123290	Fail
9889	Fail
0984035	Fail

<tbody>
</tbody>

 

[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.