Many teachers like to keep an Excel or Numbers spreadsheet to record and calculate grades across the semester. Nowadays, as more and more of our score data is being imported from an ever-widening variety of online sources, such as Google Forms and any number of quiz or Learner Management System (LMS) sites, it is becoming increasingly difficult to organize the data that we import into our spreadsheets. Nobody wants to be dragging around individual cells trying to match them up to the right students, but when you are copying in data that’s out of order, uncalculated, or incomplete, what better way is there? The answer is the Vertical Lookup Table function, or VLOOKUP, which is built into all modern spreadsheet applications.
What Is VLOOKUP?
Imagine that you give a quiz using Google Forms. You ask for each student’s name and student number and ask 10 quiz questions. If you have told Forms the answer to each question, it will automatically create a sheet showing you each student’s name, student number, and score. Great—no marking! However, these results cannot just be simply copied into your spreadsheet. Firstly, they will be sorted in the order in which they were submitted, whereas your sheet is probably ordered alphabetically or by student number. Even sorting the results data by student number will not do because there will be no empty cells or “zero” scores for the students who did not submit an answer form. If you were to just copy and paste that column’s data, you would end up with scores lining up next to the wrong students and a number of empty cells at the bottom of the column.
VLOOKUP takes care of all of this. Just tell it to look through a particular range of cells (such as those results generated by Google Forms), tell it what to look for (i.e., a specific student number), and what to come back and tell you (i.e., the corresponding score in a neighboring cell). “Hey VLOOKUP, please go searching through this disorganized data that I just imported, find student number XYZ123, and tell me their score.” Type this formula into a cell, and it will display whatever that student’s score is. Copy that formula into each cell of an entire column, and it will go and look for each student number and return each one’s score. You do not have to do any searching, dragging, or re-organizing. The app goes digging through the rows of data like a sniffer-dog, finds each specific student number that you told it to find, and then brings back their score. It is a magical timesaver.
Example
Let’s say you have a class of 10 students, and you keep a spreadsheet of their scores sorted by student number (Figure 1). You have given them a test on Google Forms, and you want to record their scores into a column called “Test 1.” To facilitate the use of the VLOOKUP formula, somewhere off to the right side of your spreadsheet, make a column called “VLOOKUP results.” (You’ll be able to use this column again and again, so having such a dedicated column over to the right of your sheet is a good idea).
Figure 1
Sample Main Sheet Set-up
Have a separate sheet within the same spreadsheet file (or a separate Table if you are using Apple’s Numbers spreadsheet), where you can paste in the results from Google Forms that you want to sort through. In the example, I called this sheet “Data import sheet,” and I pasted in four columns of data from Google Forms: time stamp, student number, name, and score (Figure 2). Notice that in this example, only 7 of the 10 students completed the test, so even if we were to simply sort this data by student number, we wouldn’t just be able to paste those scores into the main sheet without creating errors.
Figure 2
“Data Import Sheet” With Data of Seven Students
Let’s put this formula to work. On my main sheet, in Column G, I want each student’s score to magically appear. I am going start with G2 and tell this cell to go and look through the range of data that I imported into my “Data import sheet,” find this student’s student number (from Column B), and return his score information. Here’s the VLOOKUP formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
There are four parts included between the parentheses, separated by a comma and space. Let’s go through each of them:
“lookup_value” is what the formula is going to go and look for. In this case, it is the contents of Cell B2: this student’s student number.
“table_array” is the range of cells that you want it to look through to find each student number and the corresponding score information that you are seeking. In this case, it is the table of cells on the “Data import sheet” stretching between Cell B1 and Cell D7 (Figure 2). Note that we do not start from Column A: the “lookup value” (the student number), must be in the first column of the range.
“col_index_num” tells the formula which column to go to get our desired information. In this case, we want it to find the score information, which is Column D. However, the formula wants to know the number of that column within the range we selected. Column D is the third column in our range, so we will enter “3.”
The final part, “[range_lookup]” is a strange one. It basically asks, “Is it OK if I return an approximate value as opposed to exactly what’s there?” In this case, no. We do not want an approximate value, so we are simply going to answer “FALSE.”
Thus, keeping in mind that commas and spaces must be included correctly, our formula is this:
=VLOOKUP(B2, ‘Data import sheet’!B1:D7, 3, FALSE)
Like magic, when we enter that formula and hit Enter, that cell will suddenly display the value “0.9.” It looked at the student number in Column B2, looked through the range of cells we pointed it towards and found the right student number, looked across to the third column in that range, found “9/10,” and returned it.
Entering the formula is easier than it looks. Those first two values, the “lookup_value” and the “table_array”, can be entered by simply using your mouse. As soon as you type “=VLOOKUP(” , the app knows what kind of information it needs next and will understand if you just click on Cell B2. Enter a comma and a space, then make your way to your “Data import sheet,” and select the range of cells B1:D7—there is no need to type out the name of the sheet. Again, enter a comma and space followed by typing “3,” enter a final comma and space, and type “FALSE.” Hit Enter, and you are done.
Now, however, we want it to do that for each student. It is simply a matter of copying and pasting, but there is a slight catch. If we were to copy that formula down into the next cell, G3, the application will be smart enough to know that we want it to look for the student number in B3, but it might be confused as to where we want it to go looking. It might think that since we are going one row lower for the “lookup_value” (i.e., the student number), we might also want it to shift our “table_array” one row lower, too. Of course, we do not want that. We want it to look through the exact same selection of data. So, we need to tell it to keep those values no matter how far down the column we copy this formula.
The data we imported from Google Forms is in that second sheet, in Cells B1:D7. In the formula, the way to tell it to keep these exact cells is to put a “$” sign in front of each referent, like this: “$B$1:$D$7” (without the quotation marks). This locks in those values. Let’s manually edit the formula in G2 and add those “$” signs to make it this:
=VLOOKUP(B2, ‘Data import sheet’!$B$1:$D$7, 3, FALSE)
Now, when we copy that formula into every cell down the column, it will look for all the students in each row but keep looking through the exact same range of cells. Figure 3 shows what we will get.
Figure 3
VLOOKUP Results Appear With Error Notifications
Error Notifications
The formula has brought back each of those score values, but we have got some errors because there are three students who did not complete the test. Also, the formula cannot find their student numbers in the results data. There are two ways to overcome this.
Firstly, in some spreadsheet applications and versions, it might not be necessary, but our final step is going to be to copy the results in that “VLOOKUP results” column into the “Test 1” column. Select those cells with the VLOOKUP results, Cells G2:G11, and copy. Then select Column D2, the placewhere you want to paste these scores. If we simply paste what we have copied, it will paste the VLOOKUP formulas, which is not what we want. We want it to paste only the results of those formulas, so try a “Paste special,” or a “Paste formula results,” or “Paste values,” depending on what software you are using. In many cases, it will paste the score values and completely leave out those errors, making the process complete. In other cases, those ugly error notifications will be pasted in too, in which case, we need to quickly go back and include just one more step.
Let’s tell the spreadsheet what to do if it cannot find any particular student number. We do not want it to tell us there is an error; we just want it to return a score of ‘0.’ For this, we need to add the “IFERROR” formula to our VLOOKUP. The IFERROR wraps around the VLOOKUP and produces one of two results: either the VLOOKUP result, or whatever we tell it to if it cannot complete the VLOOKUP.
Let’s tell it to return a zero if it cannot complete the VLOOKUP:
=IFERROR([our existing VLOOKUP] , “0”)
This means: “Perform that VLOOKUP, but if you find an error, just give me a zero.”
Our final masterpiece formula, therefore, is:
=IFERROR(VLOOKUP(B2, ‘Data import sheet’!$B$1:$D$7, 3, FALSE), “0”)
Figure 4
VLOOKUP Results Appear
Copy that down the column, and you will have what we see in Figure 4. Wonderful!
Final Step
Now that we have got all of our score results in the correct rows in the “VLOOKUP results” column, we want to copy these results into the column for “Test 1” scores (Column D). Select and copy those VLOOKUP results, and then, again depending on your application, either “Paste formula results,” or “Paste values” into Column D. (This will ensure that only a numerical value goes into those cells, not a formula.) There is likely a keyboard shortcut for doing that special kind of paste, too.
As with everything new you learn about spreadsheet formulas, the possibilities start to grow. Rather than copy in those decimal values, why not create a simple formula in Column H to turn them into a score out of 10 or 20? In Cell H2, enter “=G2*20,” and copy it down the column. See Figure 5. Select those results, and “Paste special” into Column D. You could even do that kind of calculation in an additional column on the “Data import sheet” and have your VLOOKUP formula return that column instead of the raw score.
Figure 5
Formulating Weighted Scores From VLOOKUP Results
Reusable
Now, you have got the “VLOOKUP results” column ready to use for next time, as well as the “Data import sheet” ready to paste your next lot of results. When you paste in the results of your next test or quiz, you will likely need to edit the range of cells that your formula looks through, but once you have edited that range in the formula in G2, your top row, you can just copy it down the column and it will immediately show all of those results, ready to paste (special) right away into the column for, say, “Test 2.”
I hope this has been a helpful introduction to this wonderful formula. Now that you know it, start to play around with it, and you are sure to find more and more ways to utilize it.
Further Reading
Excel VLOOKUP Function. (n.d.). Exceljet. https://exceljet.net/excel-functions/excel-vlookup-function
Vlookup. (n.d.). Excel Easy. https://www.excel-easy.com/examples/vlookup.html
