Looking for information with a formula

[Carine_MA]

Hello everyone,

I am trying to use a formula but I am not sure how to do it. Here a description of my file:

- My excel file is compound by 2 sheets (sheet 1 and sheet 2)
About sheet 1:
- The first sheet is a list of requirements, which have name like A-1, A-2...
- Those requirements are organized in subsections and I can find the requirement A-1 (for instance) in several subsection.
- Those requirements get a status (passed/failed)

To summarize, the sheet 1 is organized like this:

Report 1
Column 1 Column 2
A-1 Failed
A-2 Failed
A-3 Passed
A-4 Passed
A-5 Passed
...
Report 2
Column 1 Column 2
A-1 Passed
A-3 Passed
A-5 Passed
A-66 Passed
...

Report 3
Column 1 Column 2
A-1 Passed
A-25 Passed
A-79 Passed
...

About sheet 2:
- The sheet 2 is the list of requirements, where we describe the requirements A-1, A-2, A-3...
The sheet 2 is organized like this:

Column 1 Column 2
A-1 XXXXX
A-2 XXXXX
A-3 XXXXX
A-4 XXXXX
A-5 XXXXX
...


I would like to be able to get the requirement status from the sheet 1. So with a formula in the column 2 in the sheet 2, grab the following info: every time A-1 appears in the column 1 in the sheet 1, check the correspondent status in column 2, if A-1 is never failed in the reports 1, 2 and 3, the status in sheet 2 is passed.

Does anyone have an idea to do this formula?
I hope my explanation is clear...

Thank you so much for your help!