How to Compare Microsoft Forms Attendance with an Excel Registration List
Question details
The user needs a method to compare event-day attendance records collected via Microsoft Forms against a master registration list stored in Excel.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking event attendance by reconciling forms responses with original registration data to determine who attended and who was absent.
- Observed behavior
- To accurately identify attendees, missing registrants, and duplicate submissions based on the available column structure and unique identifiers.
Before applying any formulas, ensure that both your exported Microsoft Forms data and your Excel registration list share a common, unique identifier column, such as an email address or a participant ID, with identical formatting.
Use XLOOKUP to Reconcile Registration and Attendance
XLOOKUP is the most robust function for comparing two lists and retrieving corresponding data based on a unique identifier.
This method requires an exact match between the unique identifiers (like email addresses) in both your Forms export and your master registration list.
Open your Microsoft Forms responses and click 'Open in Excel' to download the attendance data.
Place your Master Registration list and your Forms Attendance list in two separate sheets within the same workbook.
In your Registration sheet, add a new column named 'Attendance Status'. Enter the formula =XLOOKUP(A2, AttendanceSheet!A:A, AttendanceSheet!B:B, "Absent") where A2 is the cell containing the email in the registration list.
Drag the fill handle at the bottom-right corner of the cell down to apply this formula to all registered participants.
Use COUNTIF to Flag Attendees
The COUNTIF function provides a simple way to check if a registrant's unique identifier exists in the attendance list without retrieving additional data.
Compare Event Attendance Lists Effortlessly with WPS Spreadsheet
WPS Spreadsheet handles complex lookup functions like XLOOKUP, VLOOKUP, and COUNTIF flawlessly, making it incredibly easy to compare Microsoft Forms attendance exports with your registration lists.
- 1. Open Both Lists: Launch WPS Spreadsheet and open your downloaded Microsoft Forms attendance list and your master registration list.
- 2. Merge into One Workbook: Right-click the sheet tab of your attendance list and select 'Move or Copy' to bring it into your master registration workbook.
- 3. Use the Formula Feature: Select the cell next to your first registrant, go to the Formulas tab, and insert the XLOOKUP or COUNTIF function to cross-reference the email columns.
- 4. Verify and Save: Drag the fill handle to apply the formula to all rows, verify your attendees, and save the updated workbook as a standard .xlsx file.
Frequently Asked Questions
Why does my XLOOKUP or VLOOKUP formula return #N/A even when the emails match?
This usually happens due to hidden spaces or formatting differences between the two sheets. You can use the TRIM() function on both columns or use Find & Replace to remove trailing spaces from the data.
How can I remove duplicate attendance submissions from Microsoft Forms in Excel?
Select your attendance data range, navigate to the Data tab, and click 'Remove Duplicates'. Choose the column containing the unique identifier (like email) to keep only the first submission and remove the rest.
Can I automatically link Microsoft Forms to my Excel registration list?
Yes, if you create your Form in OneDrive or SharePoint via 'Forms for Excel', the responses will automatically sync to an online Excel workbook. You can then use Power Query to continuously merge and compare the live data with your master list.
What if my registration list doesn't have email addresses?
If you lack a single unique identifier, you can concatenate multiple columns (e.g., First Name & Last Name) using the '&' symbol in both your Forms data and Registration list. You can then use that combined column as your lookup value.
