Got it, let's break down exactly how to create that second worksheet that automatically lists every student's grade from your submission data. I'll assume your original submission sheet is named Submissions (adjust if yours has a different name), and your new sheet will be Student Grades.

Step 1: Get All Unique Student IDs #

First, we need to compile a list of every student who submitted work, combining both submitter1 and submitter2 columns while removing duplicates and empty cells.

In cell A2 of your Student Grades sheet (leave A1 as a header like "Student ID"), paste this formula:

=UNIQUE(TOCOL(Submissions!A:B, 1))

• TOCOL(Submissions!A:B, 1) converts the two submission columns into a single column, ignoring any empty cells (the 1 parameter handles that).
• UNIQUE() then strips out duplicate student IDs, so each student only appears once in the list.

Step 2: Pull Corresponding Grade for Each Student #

Next, we need to match each student ID to their grade—whether they were listed as submitter1 or submitter2 in the original submissions.

In cell B2 (set B1 as the header "Grade"), use this formula:

=INDEX(Submissions!D:D, MATCH(TRUE, (Submissions!A:A=A2)+(Submissions!B:B=A2), 0))

Here's what this does:

• The (Submissions!A:A=A2)+(Submissions!B:B=A2) part checks if the student ID in A2 matches either column A (submitter1) or column B (submitter2) in the Submissions sheet—this acts as an OR condition.
• MATCH(TRUE, ..., 0) finds the first row where this match is true.
• INDEX(Submissions!D:D, ...) pulls the grade from column D (your grade column) of that matching row.

If you want to handle students who haven't submitted work or don't have a grade yet, wrap the formula in IFERROR to show a friendly message:

=IFERROR(INDEX(Submissions!D:D, MATCH(TRUE, (Submissions!A:A=A2)+(Submissions!B:B=A2), 0)), "No grade assigned")

Example Result #

Using your sample submission data, the Student Grades sheet will populate like this:

Best part? This setup updates automatically—if you add new submissions, edit grades, or adjust student IDs in the Submissions sheet, the Student Grades sheet will refresh instantly.

内容的提问来源于stack exchange，提问作者Erel Segal-Halevi