Retrieve Student Guardian Contact Info
I have a student ID in one sheet and need to pull their guardian's email address from the school registry.
=VLOOKUP(A3,D3:G6,3,FALSE) How it works: This VLOOKUP formula efficiently searches for a specific 'Student ID' (A3) within the first column of your 'School Registry' data (D3:G6). Once it finds an exact match, it retrieves the corresponding 'Guardian Email' from the third column of that registry and displays it in your roster. Using `FALSE` ensures that only exact matches are returned, preventing data inaccuracies.
Data Setup
| "Teacher's Roster (Sheet1)" | "" | "" | "School Registry (Sheet2)" | "" | "" | "" |
|---|---|---|---|---|---|---|
| "Student ID" | "Student Name" | "Guardian Email" | "Student ID" | "Guardian Name" | "Guardian Email" | "Guardian Phone" |
| 101 | "Alice Smith" | "" | 101 | "Emily Smith" | "emily.s@example.com" | "555-1111" |
| 102 | "Bob Johnson" | "" | 102 | "David Johnson" | "david.j@example.com" | "555-2222" |
| 103 | "Charlie Brown" | "" | 103 | "Sarah Brown" | "sarah.b@example.com" | "555-3333" |
| "" | "" | 104 | "Frank White" | "frank.w@example.com" | "555-4444" |
Step-by-Step Guide
Open your Excel workbook. Ensure you have your student roster (e.g., on Sheet1) and the school registry (e.g., on Sheet2 or a separate range).
In your student roster, navigate to the cell where you want the first guardian email to appear (e.g., cell C3, next to the first student ID).
Type the VLOOKUP formula: `=VLOOKUP(`. The first argument is the 'lookup_value'. Click on cell A3 (the Student ID you want to look up).
Type a comma `,`. The second argument is the 'table_array'. Select the entire range of your school registry data, starting with the column containing the Student IDs (e.g., D3:G6). This range should include the 'Guardian Email' column.
Type a comma `,`. The third argument is the 'col_index_num'. Count the position of the 'Guardian Email' column within your selected 'table_array' (D3:G6). If 'Student ID' is column 1, 'Guardian Name' is 2, then 'Guardian Email' is 3. Type `3`.
Type a comma `,`. The fourth argument is 'range_lookup'. Type `FALSE` for an exact match, ensuring you retrieve the correct guardian email for the specific student ID.
Close the parenthesis `)` and press Enter. The guardian's email for the student in A3 should now appear in C3.
To apply the formula to other students, click on cell C3, then drag the fill handle (the small square at the bottom-right corner of the cell) down to cover all relevant student IDs in your roster.
Explore More
Count Students Who Passed the Exam
I need to quickly count how many students scored above 60 in the final math exam.
Combine First and Last Names for Roster
I have student first names in column A and last names in column B and need to merge them into 'Last, First' format.
Assign Letter Grades Based on Score
I want to automatically assign 'A', 'B', 'C', or 'F' based on the numeric score in the adjacent cell.
Sum Points for Specific Assignment Type
As a teacher, I want to calculate the total points earned across all students specifically on 'Homework' assignments, ignoring other assignment types like quizzes or exams. This helps me track overall progress for a particular category of work.