1

Problem: I would like to Sheet 1 column 4 row 2 to be automatically populated by searching Sheet 2 for a value in Sheet 2 column 3 but could be any row. I have at least 3600 assignments and 720 quizzes to grade.

I'm a teacher. Sheet 1 holds overall student grades for all assignments. This is where the formula will be. Sheet 2 is automatically populated by Google Forms as students take a quiz on the form. Students type in their name and ID in the form. Students/rows will be out of order in Sheet 2 because students may take Quiz 1 at any time. I would like their score in Sheet 1 column 4 to be automatically displayed with a formula that will search Sheet 2 column 3 for the score while the ID numbers in Sheet 2 column 2 will be out of order and not match the order of Sheet 1 column 2 ID numbers.

Sheet 1: Overall Grades

Name | ID    | Assignment 1 | Quiz 1 Score
Lucy | 09876 | 100          | 7
Todd | 12345 | 200          | 10

Sheet 2: Quiz (populated automatically by Google Form)

Name | ID    | Quiz 1 Score
Todd | 12345 | 10
Lucy | 09876 | 7
1

This is done by vlookup. A basic example is

=vlookup(B2, Sheet2!B2:C, 2, False)

which says: find the value of B2 in the first column of the range Sheet2!B2:C, and return the corresponding value from the 2nd column of that range. (The last argument, False, says the searched range need not be sorted, and exact match is required.)

A more advanced version, for looking up all students at once:

=arrayformula(iferror(vlookup(filter(B2:B, len(B2:B)), Sheet2!B2:C, 2, False)))

Additional elements here:

  • this is an arrayformula, which processes lookups for an array of data
  • the array to process is B2:B of the current sheet, but it's filtered so we don't waste time looking up empty-string values
  • the wrapper iferror suppresses error messages #N/A when no match is found; the Quiz score cell will be blank if the student never submitted the quiz.
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.