Google-sheets – How to search for a value in another sheet (same document) and return value

google sheets

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

Best Answer

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.
Related Topic