Challenge Scenario
In community membership auditing and event ticketing operations, member attendance check-ins must be validated against the active master membership roster to identify non-members or unregistered guests.
By comparing the check-in list against the master member roster with the MATCH function, coordinators can immediately verify whether an ID is valid or unlisted.
As a Membership Coordinator, your tasks are:
- Roster Check (Column D): Look up each Checked-In ID (
A2:A5) within the Master Member List in Column G ($G$2:$G$4) using MATCH(A2, $G$2:$G$4, 0).
- Membership Status (Column E): If
MATCH finds the ID, assign "MEMBER"; if MATCH returns an error (using ISNA or ISERROR), assign "UNREGISTERED".
- Total Check-Ins (Cell B8): Count total checked-in attendees using
COUNTA(A2:A5).
- Unregistered Guests Count (Cell C8): Count how many attendees have the
"UNREGISTERED" status using COUNTIF.
Your Sheet Layout
Understanding how the event check-in sheet is structured:
- Checked-In Attendee Logs (Columns A & B): Shows Member IDs and Attendee Names who arrived at the event check-in desk.
- Membership Verification (Columns D & E): Contains the
MATCH position lookup and the evaluated MEMBER vs UNREGISTERED status.
- Master Roster Table (Columns G & H): The official member database containing authorized Member IDs (
MEM-101, MEM-102, MEM-103).
- Event Audit Summary (Rows 7 & 8): Summarizes total event attendance and counts unregistered walk-in guests.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Check-In ID, Guest Name |
Check-In Log |
Scanned attendee badge IDs and names |
Read-only event check-in log. |
G2:H4 |
Active Member ID, Full Name |
Master Roster |
Authorized member registry table |
Master database. Lock with $G$2:$G$4. |
D2:D5 |
Roster Position |
Lookup |
=MATCH(A2, $G$2:$G$4, 0) |
Finds row position of ID in master roster or returns #N/A. |
E2:E5 |
Member Status |
Verification |
=IF(ISNA(D2), "UNREGISTERED", "MEMBER") |
Flags missing/unregistered IDs. |
B8 |
Total Check-Ins |
Summary |
=COUNTA(A2:A5) |
Counts total check-in entries (4). |
C8 |
Unregistered Guests |
Summary |
=COUNTIF(E2:E5, "UNREGISTERED") |
Counts unregistered attendees (1). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C5), output "MISSING" if Scanned ID is not in $E$2:$E$5 using COUNTIF, otherwise "FOUND".
2
In cell B8, count total scanned entries using COUNTA(A2:A5).
3
In cell C8, count how many IDs are "MISSING" using COUNTIF(C2:C5, "MISSING").
4
In cell D8, count how many IDs are "FOUND" using COUNTIF(C2:C5, "FOUND").
Solution & Formula Breakdown
Let's examine how MATCH and error-handling functions detect missing database records:
1. Searching the Master Roster with MATCH (D2:D5)
=MATCH(A2, $G$2:$G$4, 0)
The MATCH(lookup_value, lookup_array, match_type) searches for an ID in the master roster:
0: Specifies exact matching.- Row 2 (
MEM-101): Found at row 1 of master list → returns 1.
- Row 3 (
MEM-102): Found at row 2 of master list → returns 2.
- Row 4 (
MEM-999): Not found in master list → returns #N/A.
- Row 5 (
MEM-103): Found at row 3 of master list → returns 3.
2. Handling Missing Records with ISNA / IF (E2:E5)
=IF(ISNA(D2), "UNREGISTERED", "MEMBER")
If cell D2 contains #N/A, ISNA(D2) evaluates to TRUE and outputs "UNREGISTERED". For numeric positions, it outputs "MEMBER".
3. Event Audit Rollups (B8 & C8)
- Total Check-Ins (Cell B8):
=COUNTA(A2:A5) returns 4.
- Unregistered Guests (Cell C8):
=COUNTIF(E2:E5, "UNREGISTERED") returns 1.
You can also combine both functions directly: =IF(ISNA(MATCH(A2, $G$2:$G$4, 0)), "UNREGISTERED", "MEMBER"). This is the gold standard in Excel for membership reconciliation!