Excel Challenge 28 : Find Missing Membership IDs

What You Will Learn

Two ID lists. The active registry is in A2:A6 (M-101, M-102, M-104, M-105, M-106). The audit sequence is in B2:B6 (M-101 through M-105). You need to check which audit IDs exist in the registry and which are missing.

This is the reverse of a normal lookup. Instead of finding a match, you want to find entries that have no match. COUNTIF returns 0 when the ID is not found — that is the flag:

=IF(COUNTIF($A$2:$A$6, B2) = 0, "MISSING", "OK")

COUNTIF counts how many times the audit ID (B2) appears in the registry range (A2:A6). If it returns 0, the ID is missing. The dollar signs lock the registry range so it does not shift when you fill down.

In this dataset: M-101, M-102, M-104, M-105 all exist. M-103 does not — the registry goes from M-102 straight to M-104, skipping M-103. That is one gap.

COUNTIF checks each audit ID against the registry. Zero = missing.

The audit formulas:

B9: =COUNTIF(C2:C6,"MISSING") — counts how many IDs were missing.
B10: =IF(COUNTIF(C2:C6,"MISSING")>0,"INCOMPLETE","COMPLETE") — overall registry state. If any gaps exist, the registry is incomplete.

Alternative: MATCH + ISERROR

Instead of COUNTIF, use MATCH and catch the error when the ID is not found:

=IF(ISERROR(MATCH(B2,$A$2:$A$6,0)),"MISSING","OK")

MATCH returns a position number when it finds the ID, or an error when it does not. ISERROR detects the error and flags the row as MISSING. Same result, different approach.

The MATCH version is more common in older spreadsheets. The COUNTIF version is easier to read for beginners. Both are correct.

The same COUNTIF=0 pattern works for any "is this value missing from that list" question — IDs, names, dates, or codes. If you can describe the problem as "does this exist in that range?", this formula handles it.