"I want to build a summary student data sheet that pulls columns of unique-student ID-keyed information from other sheets"

"I want to build a summary student data sheet that pulls columns of unique-student ID-keyed information from other sheets"

Let's say you have a bunch of student data that lives in two separate sheets, where each student has a unique row of data corresponding to them.  This scenario commonly occurs when you are trying to aggregate data from multiple sources.    The VLOOKUP function is extremely handy for dealing with any scenario in which a unique row of data exists for a student and a unique lookup value, or key, for a student lives in multiple sheets. 

Here's the VLOOKUP documentation from the Google Spreadsheets function list:

TYPE NAME SYNTAX DESCRIPTION Learn More
Lookup VLOOKUP VLOOKUP(search_criterion, array, index, sort_order)

Searches vertically with reference to adjacent cells to the right. If a specific value is contained in the first column of an array, returns the value to the same line of a specific array column named by index.

  • Search_criterion is the exact value searched for in the first column of the array.
  • Array is the reference, which must include at least two columns.
  • Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
  • Sort_order (optional, defaults to TRUE) indicates that the first column in the array is sorted. If this is true, then VLOOKUP will return the closest match to the search_criterion. If false, then only exact matches will be returned.

In the example below, a "Compiled Grades" sheet is being used to gather data from other sheets.  All sheets contain student ID numbers, but because such information isn't always complete or sorted, copying and pasting is unreliable.   Instead, we need to use VLOOKUP to ensure a reliable match on the student ID number as data is compiled.  The four slides below attempt to show what each of the 4 arguments in VLOOKUP mean.  If you are new to VLOOKUP, we highly recommend you make a copy of this spreadsheet in Docs and try completing the compiled data sheet using VLOOKUP. 

 

Want to make sure you really understand this topic?  Copy this starter spreadsheet and use VLOOKUP to complete the "Compiled Grades" column.