In this lesson we’ll continue our manipulations of SIS generated data. One of the most common needs is to look up a student’s full name, email, parents’ emails, grade, etc. This becomes particularly important when we are sending out Google Forms to students. We know we can automatically collect their email addresses, but how do we use that information to look up everything else in the database?The answer is the function VLOOKUP, which stands for Vertical Lookup.It take three main parameters:
- Search Key (in this case, a student’s email address)
- Search Range (the part of the spreadsheet with the lookup data)
- Index (the number of the column you want to return in the Search Range)
In addition, there is a fourth parameter, false, which we will be using. This tells Google that the data is unsorted, which for our purposes is nearly always the case.
Take a look at this data from a Google Form, and see how VLOOKUP matches the student’s name to their email. Make a copy of the response sheet, then try entering in the formula by hand for the next two so that you can practice it. Once you have entered it by hand, try dragging the formula down so that future entries will automatically have the name looked up. Try entering in data in the form and see if it works.
To add more data, click Form > Go to live form
Google Sheets Lessons 1-9
Click on a lesson below for a written tutorial demonstrating the lesson.
- Lesson 1 – CONCATENATE
- Lesson 2 – VLOOKUP
- Lesson 3 – ARRAYFORMULA
- Lesson 4 – formatting ARRAYFORMULA
- Lesson 5 – QUERY
- Lesson 6 – UNIQUE
- Lesson 7 – INDEX & MATCH
- Lesson 8 – conditional INDEX & MATCH
- Lesson 9 – Autocrat Add-on
