Creating a Student Lookup in Google Sheets

The idea of a student lookup system has been rattling around in my head for a few years. I started this in Google Data Studio, but Data Studio started changing and I couldn't keep up. Data Studio became Looker and now four years later, it looks completely different than it did.

I have been playing with Google Sheets for a few years, and when I came to our Sheet with PYP student data, I could see that some of the data, like reading levels, was something that was outdated for most students on one sheet, but on another was kept up-to-date (EAL).

The original sheet

I started with the reading data and found that I could use VLOOKUP to connect the above sheet to the EAL sheet. The formula I used pulled the reading data from the EAL sheet and keeping our sheet updated. I also started looking for other pieces of information I could keep updated with a formula. 

This got the ideas flowing and I put my homeroom teacher hat on. I wondered what I would want to see as a homeroom teacher for a student. 

I started with a student name that would bring up strategies and goals for a student.

This was good, but then I wanted to expand it to filter between PYP, MYP, and DP. Then I wanted to know other information about the student like the number of submissions they had, reading level, language phase for MYP/DP, and a link to their support plan. I came up with this:

The updated sheet

I can see that I am not done with this - I would like to add the current number of absences, EAL goals and strategies, and something else that slipped my mind, but it is a start.