If And statement in Google sheets. Check value of a range in a row based on other values in same row, and value of a different cell in same column

I am working on an attendance and advancement tracker for a boy scout program. I am needing help to properly code an if statement. I have to verify that the scouts First, Middle, Last, and Suffix of their name are equal between two sheets. I am also coding it to auto populate this as it is entered from one sheet into the other I want to do more than just a pasting link. I can get this done, it will just take time code it right. What i need help in is with the advancement. In addition to verifying the names, it needs to verify the rank on the attendance sheet. Once again I got that ok. Here is the tricky part. The if statement also needs to see if there is a one for a date of attendance of the row of the scout. On that date of that attendance attendance, the if statement needs to check a different cell in the same COLUMN for what adventure session is being run. Sometimes, there can be multiple adventure that qualify for the same advancement. I have to use an OR statement to take this into account. Now the adventure is not necessarily going to be in the same column for each pack. The Adventure is selected by a dropdown list using data verification. I can get it to work if the attendance is in the first column, but not the corresponding ones. Do I use a range? A VLOOKUP? What is best? Keep in mind it needs to find the adventure that was chosen, THEN check to see if attendance was present in THAT column that the adventure is in, of course its also checking for the multiple name fields and the rank. It is making these checks from one sheet in the workbook, and inputting a 1 on another sheet in the workbook where the advancement is. This is a link to the document in sheets so you can see. You can make a copy and change it, or change within the link. It itself is a copy so it wont change my original.

http://ift.tt/2hntSZ9

Here is my code using the array, but of course it doesn't work. It says the array cant be found.

=if(AND('P 17-18 Attendance'!$A4=D$1,'P 17-18 Attendance'!$B4=D$2,'P 17-18 
Attendance'!$C4=D$3,'P 17-18 Attendance'!$D4=D$4,'P 17-18 
Attendance'!$L4="Lion",'P 17-18 Attendance'!O4:AZ4=1,'P 17-18 
Attendance'!O2:AZ2="BC2"),1,"")

If I use this code:

=if(AND('P 17-18 Attendance'!$A4=D$1,'P 17-18 Attendance'!$B4=D$2,'P 17-18 
Attendance'!$C4=D$3,'P 17-18 Attendance'!$D4=D$4,'P 17-18 
Attendance'!$L4="Lion",'P 17-18 Attendance'!O$4=1,'P 17-18 
Attendance'!O$2="BC2"),1,"")

It only works for column O.

These sections verify the first, middle, last, and suffix are equal:

=if(AND('P 17-18 Attendance'!$A4=D$1,'P 17-18 Attendance'!$B4=D$2,'P 17-18 
Attendance'!$C4=D$3,'P 17-18 Attendance'!$D4=D$4, ... ) 

This verifies the rank:

=if(AND( ... ,'P 17-18 
Attendance'!$L4="Lion", ... )

This is where I have the issue:

=if(AND( ... ,'P 17-18 Attendance'!O$4=1,'P 17-18 
Attendance'!O$2="BC2"),1,"")

If a cell in row 2 says "BC2", then it needs to check the cell in row 4 (or the same row that the scouts name is found as per the previous if statements) to see if it is marked as present (1). If both of these are the case, then it needs to mark the cell on the other sheet as 1. Ya know as I was writing this I thought of something else. Should I use a nested if statement to get this? I am going to try that, but if someone can please help me still on coding it right Id appreciate it.

If I can get this to work for a few of the Lion advancements, I can finish the rest. The document has an advancement matrix inside of it on a different sheet for what the other advancements are.