May 18, 2011

VLOOKUP in Google docs

Here's how to use VLOOKUP to retrieve a value in a table.  For example, I calculate student grades in numeric percentages, but I want a second column to tell me the correct grade.

My gradesheet might look like this:


I want populate the Letter Grade column with letter grades.

So in another sheet, I might have the GradeScale:

The VOLOOKUP formula would go in column B of the GradeSheet:

The actual formula looks like this: =VLOOKUP(C2;GradeScaleA$1:B$9; 2; true)

And here's the breakdown:
  • C2 = look at C2 where the numeric grade is (the Calculated Grade column; 87, 92, etc.)
  • GradeScale = the name of the sheet where my Grade Scale table is located
  • A$1:B$9 = the actual array of my table (***IMPORTANT: make sure you use the $ before the numbers-- otherwise each time you drag the formula, you'll change your array!  This is called absolute referencing,)
  • = return the value in column 2 (if you enter 1, it'll return the value in column 1) of the GradeScaleTable
Now just drag the formula down all the cells where you want the VLOOKUP to function. 

According to Google Docs help:
Syntax: VLOOKUP(search_criterion, array, index, sort_order)
  • 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.

No comments:

Post a Comment