How to match Google Forms responses to a master list

Set up

1. In the form, turn on Collect email addresses (Settings → Responses) so every response has a reliable email.
2. Link responses to a sheet: Responses → Link to Sheets. Responses land in a tab called Form Responses 1, with Timestamp in column A.
3. Keep the master list (name, email, team) on another tab of the same spreadsheet, or bring it in with IMPORTRANGE.

Status and answers

Status (C2):
=ARRAYFORMULA(IF(A2:A="", , IF(COUNTIF(LOWER('Form Responses 1'!B2:B), LOWER(A2:A)) > 0, "Submitted", "Missing")))

Latest answer to question 1 (D2):
=ARRAYFORMULA(IF(A2:A="", , IFERROR(XLOOKUP(LOWER(A2:A), LOWER('Form Responses 1'!B2:B), 'Form Responses 1'!C2:C, "", 0, -1))))

The -1 makes XLOOKUP search from the bottom, so a person who submitted twice shows their latest answer.

Completion by team

=QUERY(A2:C, "select B, count(A) where A is not null group by B pivot C", 0)

This counts Submitted and Missing per team in one table — handy for chasing managers.

Responses from people not on the master list: =FILTER('Form Responses 1'!B2:B, COUNTIF(LOWER(A2:A), LOWER('Form Responses 1'!B2:B)) = 0). Usually a personal email used instead of a work one.

Sending reminders

Copy the Missing rows into a mail merge, or filter them and send from your email client. If you resend the form, share the same link — a second form creates a second responses tab, and the formulas above would need to look at both.

Common problems

• People who submit with a personal Google account when you expected their work email — turn on Restrict to users in your organisation if your form is internal.
• Edited responses: Google Forms updates the original row, so the latest answer is already in place.
• Extra spaces or capitals in the master list — the LOWER in the formulas handles capitals; add TRIM if the list was pasted from elsewhere.

Step by step from a blank sheet

1. Open the linked responses spreadsheet and add a tab called Master.
2. Paste or import the master list with email in column A, name in B and team in C (adjust the formulas if your columns differ).
3. Put the Status formula in the first empty column of row 2; ARRAYFORMULA fills it down for every row.
4. Add one XLOOKUP column per question you want to see next to each person.
5. Use a filter or a filter view on Status to show only Missing.

Filter views let each person chasing responses see their own filtered list without changing what others see. Because the formulas use whole columns, new responses and new people on the master list are picked up automatically.

Second example: deadline and late submissions

The Timestamp column lets you separate on-time and late submissions. With the deadline in a cell named Deadline:

Submitted at (E2):
=ARRAYFORMULA(IF(A2:A="", , IFERROR(XLOOKUP(LOWER(A2:A), LOWER('Form Responses 1'!B2:B), 'Form Responses 1'!A2:A, "", 0, 1))))

On time? (F2):
=ARRAYFORMULA(IF(E2:E="", , IF(E2:E <= Deadline, "On time", "Late")))

The search mode 1 here returns the first submission, which is the right one for judging lateness; the earlier formula with −1 returns the latest answer.

Troubleshooting

To check the result, the number of Submitted rows plus the number of responses from people not on the list should equal the number of distinct emails in the responses tab. For linking a master list held in another file, see linking two Google Sheets and using QUERY with multiple ranges.

Matching on an ID instead of email

When people may not have a Google account, or the form is anonymous to Google, add a short-answer question for employee or student ID and use response validation to require the expected format. Match on that column instead of email, and keep the same TRIM and LOWER cleaning, because typed IDs pick up spaces. You can also send each person a pre-filled link with their ID already entered (Get pre-filled link in the form's menu), which removes typing errors entirely.

Doing it with QUERY or Excel

If you prefer one formula that lists everyone still missing with their team:

=FILTER(Master!A2:C, Master!A2:A <> "", COUNTIF(LOWER('Form Responses 1'!B2:B), LOWER(Master!A2:A)) = 0)

In Excel, download the responses as a .csv or .xlsx, put both lists in tables and use =COUNTIF(Responses[Email], [@Email]) > 0 for status; COUNTIF ignores case in Excel and Google Sheets. In Excel 365, FILTER works as above; in Excel 2019, filter the status column instead.

Common mistakes

• Sorting the Form Responses tab — new responses keep arriving at the bottom and formulas pointing at fixed rows break. Sort a copy or use a filter view instead.
• Adding formulas inside the Form Responses tab, where new rows can overwrite them.
• Unlinking and relinking the form, which creates a new responses tab the formulas don't see.
• Comparing names instead of emails, so a missing middle name marks someone as Missing.

Repeating it for the next round

For a quarterly form, make a copy of the whole spreadsheet with the form, clear the responses, and update the master list. The copied formulas keep working, and the previous round stays intact for comparison. If you need to compare rounds, import each round's status column into one summary tab with IMPORTRANGE and count Submitted per team per round.

Frequently asked questions

How do I see who hasn't filled in my Google Form?

Link responses to a sheet and use COUNTIF of each master-list email in the responses; zero means not submitted.

How do I get a person's latest response if they submitted twice?

Use XLOOKUP with search mode -1 to search from the bottom of the responses.

Can I match form responses without collecting emails?

Only on another unique field such as an employee ID. Names are unreliable for matching.

Can I do this if the master list is in another spreadsheet?

Yes. Import it with IMPORTRANGE onto a tab in the responses spreadsheet, or import the responses into the master list's file.

How do I see who submitted after the deadline?

Look up each person's first Timestamp with XLOOKUP and compare it with the deadline cell.

Will the formulas pick up new responses automatically?

Yes. Because they refer to whole columns with ARRAYFORMULA, new rows in the responses tab are included as soon as they arrive.

Can I match responses on an employee ID instead of email?

Yes. Add an ID question with response validation, or send pre-filled links, and use that column in the COUNTIF and XLOOKUP formulas instead of email.

Related guides

• How to merge Google Sheets into one
• How to combine multiple IMPORTRANGE formulas
• How to SUMIF across multiple sheets in Google Sheets
• How to merge sheets with Google Apps Script
• How to use QUERY with multiple ranges in Google Sheets