How to find values in one list that are missing from another in Excel
Excel formula
=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")Keep the items the other list doesn't contain: =FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid").
- Result on the example
- a 3-row list that spills from E2 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF counts each invoice in the paid list. A count of 0 means it was never paid, so FILTER keeps those rows: INV-2204, 2206 and 2208. When every invoice is paid, the third argument shows "All paid" instead of an error.
=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")Result{3 rows × 1 col} "INV-2204", "INV-2206", "INV-2208"
A2:A109 rows × 1 col: "INV-2201", "INV-2202", "INV-2203", "INV-2204", …A range: 9 cells in one column.
C2:C76 rows × 1 col: "INV-2203", "INV-2201", "INV-2207", "INV-2205", …A range: 6 cells in one column.
COUNTIF(C2:C7,A2:A10){9 rows × 1 col} 1, 1, 1, 0, …COUNTIF: Counts cells that meet one condition.- range
C2:C76 rows × 1 col: "INV-2203", "INV-2201", "INV-2207", "INV-2205", … - criteria
A2:A109 rows × 1 col: "INV-2201", "INV-2202", "INV-2203", "INV-2204", …
- range
COUNTIF(C2:C7,A2:A10)=0{9 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid"){3 rows × 1 col} "INV-2204", "INV-2206", "INV-2208"FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:A109 rows × 1 col: "INV-2201", "INV-2202", "INV-2203", "INV-2204", … - condition1
COUNTIF(C2:C7,A2:A10)=0{9 rows × 1 col} FALSE, FALSE, FALSE, TRUE, … - condition2, ...
"All paid"
- range
In Google Sheets
Google Sheets needs a different formula: =IFERROR(FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0),"All paid"). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:A10,ISNA(MATCH(A2:A10,C2:C7,0)),"All paid") | spills 3 × 1 | The MATCH version: an invoice MATCH can't find returns #N/A, and ISNA keeps it. |
Common mistakes
- Check the direction. This lists invoices without payments; payments without invoices need the lists swapped.
- IDs stored as numbers in one list and as text in the other never match. Convert one side first.
- Spaces must match exactly: an ID with a trailing space is a different ID. Clean both lists with TRIM if they come from different systems.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find values in one list that are missing from another
E2, list the invoices in A2:A10 that don't appear in the paid list C2:C7.Questions
How do I find the missing items in both directions?
Run it twice with the ranges swapped, or stack the results: =VSTACK(FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0),FILTER(C2:C7,COUNTIF(A2:A10,C2:C7)=0)).
How do I count the missing items?
Use =SUMPRODUCT(--(COUNTIF(C2:C7,A2:A10)=0)).
How do I list the items that are in both lists?
Keep counts above zero: =FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)>0).
How do I flag each row instead of making a list?
In B2, filled down: =IF(COUNTIF($C$2:$C$7,A2)=0,"Missing","").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.