Spreadsheet guide · Report card imports

Report Card ID Match

How to put the right student ID on every report card row with a few spreadsheet formulas, and turn red anything a person needs to check. Every name and ID on this page is made up.

Download the PDF guide

The problem it solves

The import needs IDs

Grade and comment exports often have student names but no student ID. Most report card and student data systems match each row to a student by ID, so every row needs one.

Copy and paste is risky

Lining two alphabetical lists up side by side works until one row is off. Then every ID below it lands on the wrong student, and nothing looks wrong.

Same names are a trap

With two students named Isaiah Brooks, a simple lookup grabs the first one and never warns you. The sheet is built so that can't happen silently.

How it works, in four steps

Set up a sheet with two tabs. IDs is a list you trust that already has student IDs, such as a dismissal or enrollment roster. Grades is the export that needs IDs, one row per student per class. The formulas go in the blue-header columns.

Build a match key on both tabs

The key is last name, first name and grade joined into one piece of text, with extra spaces removed and everything in lowercase. The same recipe runs on both tabs, so the same student produces the same key on each.

Grades F2=LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))&"|"&TRIM(C2)
IDs E2=LOWER(TRIM(A2))&"|"&LOWER(TRIM(B2))&"|"&TRIM(D2)

Why: a spreadsheet compares text exactly, so " chen " and "Chen" look like different people. The key throws away what doesn't matter (spacing, capitals) and keeps what does. Grade is in the key so two students with the same name in different grades stay apart.

Match key:

Count the matches before trusting one

For each report card row, count how many students on the IDs tab have the same key.

Grades H2=COUNTIF(IDs!E:E,F2)

Why: the count tells you whether the answer is safe. 1 means exactly one student, a clean match. 0 means no one on the ID list matches. 2 or more means two students share that name and grade, so the sheet can't know which one is meant.

Pull the ID only when there is exactly one match

Grades G2=IF(H2=1,XLOOKUP(F2,IDs!E:E,IDs!C:C),"CHECK")

Why: a lookup on its own returns the first match it finds, even when there are two. Checking the count first means an ID only appears when it's certain. Anything else says CHECK, so a wrong ID never slips in quietly.

Turn the problems red

Select the Grades rows and add a conditional formatting rule that uses a formula. In Google Sheets: Format, then Conditional formatting, then "Custom formula is." In Excel: Home, then Conditional Formatting, then New Rule, then "Use a formula." Enter the rule below and set the fill to red. A second rule, =$H2=1, colors the clean rows green.

Rule=$H2<>1

Why: with hundreds of students and several classes each, nobody can read every row. Green rows are done. You only look at the red ones.

Plus one check in the other direction

On the IDs tab, a count of each student's rows on the Grades tab catches a student who has no report card data at all, for example a student who isn't in a report card batch yet.

IDs F2=COUNTIF(Grades!F:F,E2)

Try it

This is a small Grades tab, working live. Edit any name or grade, or use a button to fix one of the red rows, and watch the row turn green.

ALast NameBFirst NameCGradeDClass FMatch KeyGStudent IDHMatchesIStatus

IDs tab

ALast NameBFirst NameCStudent IDDGrade EMatch KeyFRows on GradesGStatus

The two Isaiah Brooks rows stay red on purpose. Same name, same grade: only a person can tell which report card belongs to which student, using class or homeroom.

What each red row means and how to fix it

Nickname
Mike on the export, Michael on the ID list. Change one so both match, then the row turns green.
Accents or hyphens
Jose Ramirez against José Ramírez, or a hyphenated last name typed two ways. The key keeps accents, so fix the spelling on one tab.
Wrong grade
A row with the student's grade typed wrong. Correct the grade and it matches.
Same name, same grade
Two students named Isaiah Brooks in grade 4. Type the right ID into those rows by hand, using class or homeroom to tell them apart.
No report card rows
Red on the IDs tab. The student has no grades in the export, which often means they were left out of it or haven't been set up for this grading period yet.

Using it with your own files

  1. Paste the ID list into IDs columns A to D (last name, first name, student ID, grade) and the export into Grades columns A onward.
  2. If a file has the whole name in one column, split it first so last and first name are separate (Data, then Split text to columns in Google Sheets; Data, then Text to Columns in Excel). If the export has no grade column, drop grade from the key on both tabs; same-name students will then show as red rows to check.
  3. Copy the blue-header formula columns down to the last row. If your columns are in different places, change the letters in the formulas to match.
  4. Fix the red rows until everything is green.
  5. Before importing everything, send five to ten finished rows to whoever runs the import, or the vendor's support team, and ask them to confirm the IDs land on the right students. Schools often have more than one ID per student, such as a state ID and the student information system's own ID, so this confirms you're using the one the import expects before the whole file goes in.

The comments export gets the same treatment: paste it into another Grades-style tab and reuse the same key and formulas.

Teaching it to someone, about 15 minutes

2 minThe problemWhy copy and paste breaks, and the wrong Isaiah risk.
4 minThe four stepsKey, count, pull the ID, turn problems red. Use the key builder above.
4 minTry itFix the three red rows in the demo and watch them turn green.
5 minTheir real filesPaste in the ID list and one export, fill the formulas down, and read the first red rows together.