IF Formula, read multiple columns and check for result and return name in other column.

Hi guys, I have a workbook that has a table built into it. I use it to track students who take a test. I need to come up with a formula that will look at the students results in the column and then look at the second result, then the third result, and finally return the students name who has not passed the test.

excel.gif

this is a simple rendition of my sheet but I am need to use an array in the formula. something like below...

=IF('FY22 Student Tests'!I:I,"FAIL",'FY22 Student Tests'!P:P,"FAIL",OR "';'FY22 Student Tests'!Y:Y,"FAIL"OR"";'FY22 Student Tests'!B:B)

In this horrible formula, column I holds the first test result, P holds the second test result, Y holds the third result and B holds the student name. What I am really interested in finding here is the names of students who have failed and do not have a passed test.

Any help here would be greatly appreciated.

There are instructions at the top of the page explaining how to attach your sample workbook.

fixed and sample uploaded

Thanks. And are you still using Excel 2010?

That's good news!

In AA2 copied down:

=IF(B2="","",IF(OR(I2="Pass",P2="Pass",Y2="Pass"),"",B2))

Well, if you clarify what you need, I’m sure we can sort it out.