Look up values across two columns and display value from third column

[lolly150]

Please can someone help with my dilemma:

I need to notify line managers which of their team are out of date with training requirements

Colum A is Line Manager
Column B is staff member
Columns C-E are for training results, Capacity,Fire and Equality. The only entry in these columns would be "non compliant", although some cells may be blank.

I'd like a formula to look up a specific line manager in column A, e.g: Mandy Jones, then look up "non compliant" in column C for example and then show the result as the corresponding staff member.

Is any one able to help with this?

 

[Special-K99]

I'm assuming the following, you'll need to adjust the formula to match your data

in Sheet1 A1 specific line manager

in A3
=IFERROR(INDEX(Sheet2!$B$1:$B$1000,SMALL(IF((Sheet2!$A$1:$A$1000=A1)*((C$1:C$1000="non compliant")+(D$1:D$1000="non compliant")+(E$1:E$1000="non compliant")),ROW($A$1:$A$1000)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter

or if you can use AGGREGATE

=IFERROR(INDEX(Sheet2!B$1:B$1000,AGGREGATE(15,6,ROW(A$1:A$1000)/(Sheet2!A$1:A$1000=A1)*((C$1:C$1000="non compliant")+(D$1:D$1000="non compliant")+(E$1:E$1000="non compliant")),ROWS(A$1:A1000))-(1-1),1),"")

and copy whichever formula you use down as far as however many rows of data there are in Sheet2

 

[lolly150]

Thanks for your reply.
I'm a little confused about where I need to enter the formula. My data is on a sheet named 'Results' and is about 200 rows. The training results data (i.e. non compliant) is in columns C-U

 

[Special-K99]

That information wasn't in your original description.
Also in your original description you said "non compliant" was in columns C-E not C-U.

I'll have to rethink the formula, not sure this is gonna work now...