|The simplest way is to add a Helper column.|
On Sheet 1 insert a new column between B & C
then CONCATENATE columns A & B, like:
A B C D
1) Class A John Class AJohn Attended
2) Class A Peter Class APeter Not attended
3) Class B Peter Class BPeter Not attended
4) Class B Mary Class BMary Attended
5) Class B David Class BDavid Attended
6) Class C David Class CDavid Attended
7) Class C John Class CJohn Attended
8) Class C Peter Class CPeter Not attended
In your New Column C, simply enter =A1&B1
drag down as many rows as needed.
Now it becomes a simple =VLOOKUP()
On Sheet 2, Cell B2 enter the formula:
The ISNA() function is there to prevent the error
message #N/A when no match is found,
like where John has no records for Class B.
You can also Hide your Helper column if you want.
See how that works.