How do I return a true or false based on multiple criteria in Google sheets?

Question

In my Google sheets workbook I have one tab (call it Classlist) that has rows with the student's last name, first name, email address, and a series of checkboxes for the quizzes in the course. The columns are the names of the quizzes. In another tab (call it Grades) I have the student's last name, first name, quiz taken, and pass/fail. What I'm trying to do is create a formula that will search Grades and if the particular student has passed a particular quiz it checks the checkbox in Classlist. So The last name, first name, quiz, and grade have to be right and it has to return a true or false in order to check the box. I've tried "IF/AND" formulas, VLookup, I even installed an add-on for Xlookup, but I can't get it to work. Any ideas?

Answer 1

So if I guessed correctly your tables look something like this:

in this case you can do it with a filter function like this:
=FILTER( Classlist!PassColumn, Classlist!$LastNameColumn = Grades!$LastNameColumnCurrentRow, Classlist!$FirstNameColumn = Grades!$FirstNameColumnCurrentRow, Classlist!$QuizColumn = Grades!CurentColumn$QuizRow)

and you can drag it down/ copy to other cells.

So for example this would look like this in practice:

Only that you have to complete it with your actual values. Pay attention to protect the proper rows/columns in each case with $.

Note that this will only work correctly if none of your student have multiple entries for the same quiz.