Lookup & Reference
Find missing values between two lists: reconcile invoices with COUNTIF and FILTER
Flag items in List A that are absent from List B using COUNTIF or XMATCH, and spill an instant exception report with FILTER.
Two-step audit pattern: row status + filtered exception list
During bank or inventory reconciliation, you want both a row-by-row audit flag next to every issued invoice AND a compact list of unpaid/missing items.
COUNTIF($D$2:$D$4, A2)=0 tests whether A2 is absent from ClearedList (D2:D4). Filtering A2:A5 where AuditStatus="Missing" spills only the unmatched IDs.
| IssuedInv (A) | AuditStatus (B) | ClearedInv (D) |
|---|---|---|
| INV-10 | Cleared | INV-10 |
| INV-11 | Missing | INV-12 |
| INV-12 | Cleared | INV-99 |
| INV-13 | Missing |
Spill all missing IDs using FILTER's 3rd argument (if_empty)
Passing "All reconciled" as FILTER's optional 3rd if_empty argument outputs a clean confirmation message once the final missing invoice clears—without wrapping in IFERROR.
=TEXTJOIN(", ", TRUE, FILTER(A2:A5, B2:B5="Missing", "All reconciled"))Count unmatched items
COUNTIF(B2:B5, "Missing") gives the open exception count (2).
=COUNTIF(B2:B5, "Missing")Try the example
Use the original values or change them to test your understanding.