A scan log full of IDs is accurate and unreadable. S1042, S1043, V0007: nobody at the end of a session can tell from that column who came. The fix is not to put names into the codes. It is a lookup: one tab that says which name belongs to which ID, and a formula that reads it.
Everything in this guide is ordinary Google Sheets and costs nothing. It works whether the IDs reached the sheet through a Google Form, a USB barcode scanner typing into a cell, or a scanner link. The column letters below follow the default QR to Sheets row; adjust them to your own sheet.
Written by QR to Sheets, which sells the scanning half described near the end, so weigh that accordingly. A QR to Sheets scanner link does not hold your list of people or look names up for you: the list stays in your own Google Sheet, where you decide who can see it. (The paid Events add-on is the exception: it imports a guest list and shows each guest's name at check-in.) Google Sheets behaviour was read from Google's own help pages on 2026-10-06.
The layout: a People tab, a scan tab and a Report tab
- People: one row per person. ID in column A, name in column B, class, team or company in column C. This is the only place a name is typed, so a correction is made once and every report picks it up.
- Log: where the scans land, one row per scan. In a QR to Sheets sheet, column A is the scan time, column B the scanned value, column C the scanner link it came through, written as link:Main gate, then the workspace name, the device type and a scan ID. A Google Form response sheet has Google's Timestamp in column A and your questions after it.
- Report: formulas only. It reads the other two tabs and is safe to sort, filter, chart and share, because nothing on it is the original record.
Make the IDs match as text
This is the trap that makes a correct formula return nothing. QR to Sheets writes the scanned value exactly as it was read, as text, so 1042 in the Log tab is the text 1042, with any leading zeros kept. If you type 1042 on the People tab, Google Sheets stores a number, and a text 1042 never matches a number 1042. Every row then shows as missing from the list.
- Best: give IDs a letter prefix, such as S1042 for students or V0007 for volunteers. A prefixed ID is always text, keeps its zeros and cannot be mistaken for a quantity.
- If your IDs must be digits, select the People tab's column A and choose Format, Number, Plain text before typing or pasting them.
- Check both sides: =ISTEXT(People!A2) and =ISTEXT(Log!B2) should both say TRUE. A Google Form response sheet can hold a typed number as a number, so run the same check there.
- Stray spaces break matches too. If IDs were pasted from another system, wrap the lookup value in TRIM().
Getting IDs into the sheet for free
The lookup does not care how the IDs arrived, so start with whatever is free and already to hand:
- A Google Form with one short-answer question for the ID, opened from a printed QR code. Each submission becomes a row with a real timestamp. People type their own ID, so expect typos, and every typo will surface later as a row that is not on the list. The full free setup for a school is in QR code attendance for schools.
- A USB or Bluetooth barcode scanner plugged into a laptop. It types the ID into whichever cell is selected, which is fast at one desk. It does not add a time on its own, and a NOW() formula beside it recalculates, so it cannot serve as a record of when.
- Typing IDs from a paper sheet afterwards, which works for a small group and is where most people start.
Show the name next to a scan: XLOOKUP, VLOOKUP or INDEX and MATCH
All three do the same job: find the scanned ID in the People tab and return the name on that row. Each example looks up the ID in Log!B2 and shows Not on the list when the ID is missing, instead of an #N/A error.
- XLOOKUP: =XLOOKUP(Log!B2, People!A:A, People!B:B, "Not on the list"). The fourth argument is what to show when there is no match, so no IFNA is needed.
- Name and class together: =XLOOKUP(Log!B2, People!A:A, People!B:C, "Not on the list"). Google's help notes that when the result range is more than one column, XLOOKUP returns the whole matching row, so the name and the class spill into two cells.
- VLOOKUP: =IFNA(VLOOKUP(Log!B2, People!A:C, 2, FALSE), "Not on the list"). The FALSE matters. Without it VLOOKUP assumes the list is sorted and can return the nearest ID, which is a wrong name rather than an error.
- INDEX and MATCH: =IFNA(INDEX(People!B:B, MATCH(Log!B2, People!A:A, 0)), "Not on the list"). Useful when the ID column sits to the right of the name, which VLOOKUP cannot handle.
Use XLOOKUP if you only work in Google Sheets. VLOOKUP and INDEX with MATCH are worth knowing because they also work in older spreadsheets and in files that will be opened in other tools.
A Report tab that covers new rows on its own
The tempting move is a formula in column G of the Log tab, filled down beside the scans. Do not. New scans arrive as new rows at the bottom, and a formula filled down beside earlier rows does not reliably follow them, so the names stop partway down. Typing on the scan tab also puts the original record one slip away from an edit.
Instead, put one formula per column in row 2 of a Report tab, with headers such as Time, ID, Name and Class in row 1. Each formula covers every row of the Log, including rows that have not arrived yet:
- Time, in A2: =ARRAYFORMULA(IF(Log!B2:B="", "", Log!A2:A))
- ID, in B2: =ARRAYFORMULA(IF(Log!B2:B="", "", Log!B2:B))
- Name, in C2: =ARRAYFORMULA(IF(Log!B2:B="", "", IFNA(VLOOKUP(Log!B2:B, People!A2:C, 2, FALSE), "Not on the list")))
- Class or team, in D2: the same as C2 with 3 in place of 2.
- Leave everything below row 2 empty. An array formula needs room to fill, and a typed value in its way stops it with a #REF! error.
VLOOKUP is used here because it accepts a whole column of IDs inside ARRAYFORMULA in one formula. To highlight problems, add a conditional formatting rule to the Report tab with the custom formula =$C2="Not on the list".
What Not on the list is telling you
- A code was printed for someone who was never added to the People tab. Add the row and the report corrects itself.
- A type mismatch: text in one tab, a number in the other. Run the ISTEXT check above.
- A typed ID with a typo or a trailing space, which only happens where IDs are typed rather than scanned.
- A code that was never one of yours, such as a product barcode scanned by mistake. The row is still worth keeping; it shows what happened.
Who has not scanned today?
A scan log only records who came. Absence is the People list minus today's scans. One formula on its own tab lists the ID and name of everyone with no scan dated today:
- Missing today: =FILTER(People!A2:B, People!A2:A<>"", ISNA(MATCH(People!A2:A, FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY()), 0)))
- Before the first scan of the day the inner FILTER finds nothing, and the formula correctly lists everyone.
- Count of people present today, each counted once: =IFERROR(ROWS(UNIQUE(FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY()))), 0)
- One door or one session only: add a condition to the inner FILTER, such as Log!C2:C="link:Main gate", using the exact name of the scanner link.
- A past date: replace TODAY() with DATE(2026,10,6), or with a cell holding the date, so one tab can answer for any day.
- A Present column on the People tab, in D2: =ARRAYFORMULA(IF(A2:A="", "", IF(ISNUMBER(MATCH(A2:A, FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY()), 0)), "Present", "Not yet")))
Why VALUE() is there: QR to Sheets writes each time as YYYY-MM-DD HH:MM:SS in the workspace timezone, and Google Sheets stores it as text. VALUE() turns that text into a date and time, INT() drops the time, and the result can be compared with TODAY(). VALUE() also accepts a real date, so the same formulas work on a Google Form response sheet. Check your own column with =ISTEXT(Log!A2).
TODAY() follows the spreadsheet's own time zone, set in File, Settings. Set it to the same zone as your scans, or for an hour or two around midnight today will mean the wrong day.
One person, several scans
Someone who scans twice appears twice on the Report tab, which is right for a log: the second scan may be a return after lunch or a nervous double tap. For a register with each person once, build it from the unique IDs scanned today and look the names up from there:
- In A2 of a Present tab: =IFERROR(UNIQUE(FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY())), "") (blank until the first scan of the day)
- In B2: =ARRAYFORMULA(IF(A2:A="", "", IFNA(VLOOKUP(A2:A, People!A2:B, 2, FALSE), "Not on the list")))
- For the first scan time of each person, see the first-scan flag in handling duplicate QR scans.
On a QR to Sheets scanner link, a repeat is recorded and flagged by default: when the same code repeats on a link within 12 hours the screen turns amber and says Saved anyway. Keep the link's Accept duplicate scans setting on for daily attendance. Switched off, a link records each code once, ever, and would refuse tomorrow's scan.
Should the code hold the ID only, or the ID and the name?
Two designs work. The ID-only code with a People tab is the better default for anything printed and carried:
- A lost card shows S1042 and nothing else. Any phone camera can read a QR code, so whatever is inside it is readable by whoever finds it.
- A name change, a new class or a corrected spelling is one cell on the People tab, not a reprinted card.
- Two people with the same name stay two people.
The alternative is a code that carries both, such as S1042|Ana Lopez, split into separate columns as it is scanned. Column mapping in QR to Sheets does this, and it suits a one-off event where nobody will build a People tab. Know the costs before choosing it: the name is printed inside every code, a name change means a new code, and with column mapping on the row layout changes. A mapped row starts with the scan time in column A, the link name in column B and a device ID in column C, and your own columns begin at column D. Point the formulas above at column D instead of column B.
Where QR to Sheets fits
QR to Sheets is the scanning half of this layout. You connect your Google Sheet and create a scanner link pointed at the Log tab. Staff, teachers or volunteers open the link in their phone browser, allow the camera once and scan each person's printed code. There is no app and no account for the person scanning, and each scan lands as a timestamped row on the Log tab. The People tab and the Report tab are yours, built with the formulas above.
- Printing the codes: paste the People tab's ID column into the free bulk QR code generator, which needs no signup and lays the codes out on label sheets.
- No signal: scans are kept on the phone and sent when the connection returns, keeping the time they were scanned.
- The people scanning never get access to the spreadsheet, so the People tab and its names stay with whoever you share the file with.
- A damaged or forgotten card: the scanner has a manual entry field for typing the ID, which writes the same kind of row.
Be clear about one limit. On a scanner link the phone shows the code it scanned, such as S1042, and a confirmation; it does not show the name, because the name lives in your sheet rather than in QR to Sheets. The Events add-on, which imports a guest list, does show each guest's name at the door. If the person at the door needs to see names, keep the Report tab open on a laptop or tablet beside them, or use codes that carry the name and accept the privacy cost above.
| Capability | Google Form + lookup formulas | QR to Sheets |
|---|---|---|
| Cost | Free | Free to 300 scans in total, one link and one phone; then 7 USD per registered device per month |
| How the ID gets in | Typed into a form field | Scanned from a printed code, or typed when a card is missing |
| Typos in IDs | Common, and each one shows as Not on the list | Rare, because the code is read rather than typed |
| Names next to IDs | Lookup formulas you write | Lookup formulas you write |
| Name shown on the phone | Only if the person types it | No on a scanner link (the Events add-on shows guest names) |
| Timestamp | A real date in the spreadsheet's time zone | Text in the workspace timezone, read with VALUE() |
| No signal | Cannot submit | Kept on the phone, sent later |
| Where the data lives | Your Google Sheet | Your Google Sheet |
On price: the free plan is one scanner link, one Sheet, one registered device and 300 scans in total, not per month, which covers one door or one class for a trial. Beyond that it is 7 USD per registered device per month with unlimited scans and links. One phone at the door is one device, however many people it scans.
Step by step
- 1
Build a People tab
Put each ID in column A, the name in column B and anything else you report on, such as class or team, in column C. Format column A as plain text before typing, so the IDs match what the scanner writes.
- 2
Put only the ID in each code
Print one code per person from the ID column. A code that holds only S1042 reveals nothing if it is lost, and a name change means editing one cell rather than reprinting a card.
- 3
Leave the scan tab alone
Let scans land on their own tab, here called Log. Do not type on it, sort it or fill formulas down beside it. It is the record everything else is built from.
- 4
Add a Report tab with array formulas
One ARRAYFORMULA per column at the top of a Report tab copies each scan across and looks the name up, and it covers new rows as they arrive without anyone touching it.
- 5
Add a list of who has not scanned today
A FILTER over the People tab that keeps every ID with no scan dated today is your absence or no-show list. It updates itself through the day.
Frequently asked questions
How do I show a name when an ID is scanned into Google Sheets?+
Keep a People tab with each ID and name, then use =XLOOKUP(Log!B2, People!A:A, People!B:B, "Not on the list") on a separate Report tab. For a version that covers new rows automatically, use VLOOKUP inside ARRAYFORMULA in row 2 of the Report tab.
Why does my lookup say Not on the list for everyone?+
Almost always a type mismatch: the scanned IDs are text and the People tab holds numbers, or the other way round. Check both with ISTEXT. Give IDs a letter prefix, or format the People ID column as Plain text before typing them.
Why does VLOOKUP return the wrong name?+
The last argument is missing. VLOOKUP assumes a sorted list unless you pass FALSE, and on an unsorted list it can return a nearby ID's name instead of an error. Use =VLOOKUP(ID, People!A:C, 2, FALSE), or use XLOOKUP, which matches exactly by default.
Does QR to Sheets look up names itself?+
No. It writes the scanned code as a row in your Google Sheet, and you add names with a lookup against your own People tab. The list of people never leaves your spreadsheet, and a scanner link's phone shows the scanned code rather than a name. The paid Events add-on is different: it imports a guest list and shows each guest's name at check-in.
How do I list who has not scanned today?+
Filter the People tab for IDs with no scan dated today: =FILTER(People!A2:B, People!A2:A<>"", ISNA(MATCH(People!A2:A, FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=TODAY()), 0))). The VALUE() is needed because QR to Sheets timestamps are stored as text.
Should I put the person's name in the QR code?+
Usually not. Any phone camera can read a QR code, so a lost card exposes whatever it holds, and a name change means reprinting. Put an ID in the code and keep the name on a People tab. Codes that carry both can be split into columns with column mapping when a list is not practical.