# How to find values in one list that are missing from another in Excel

Keep the items the other list doesn't contain: `=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")`.

**Excel:** `=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=IFERROR(FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0),"All paid")`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Invoice |  | Paid |  | Unpaid invoices |
| INV-2201 |  | INV-2203 |  |  |
| INV-2202 |  | INV-2201 |  |  |
| INV-2203 |  | INV-2207 |  |  |
| INV-2204 |  | INV-2205 |  |  |
| INV-2205 |  | INV-2209 |  |  |
| INV-2206 |  | INV-2202 |  |  |
| INV-2207 |  |  |  |  |
| INV-2208 |  |  |  |  |
| INV-2209 |  |  |  |  |

Formula in E2: `=FILTER(A2:A10,COUNTIF(C2:C7,A2:A10)=0,"All paid")`

Result (spills 3 × 1): INV-2204; INV-2206; INV-2208

## 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.

## Other ways

- `=FILTER(A2:A10,ISNA(MATCH(A2:A10,C2:C7,0)),"All paid")`: 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.

## 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","")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/values-missing-from-another-list