# How to find invoices that are in one list but not the other in Excel

Count each invoice in the other list: =IF(COUNTIF($E$2:$E$7,A2)=0,"Not in bank","Matched"). A count of 0 means the item is missing.

**Excel:** `=IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Ledger invoice | Amount | Status |  | Bank invoice | Amount |
| INV-2001 | 540 |  |  | INV-2001 | 540 |
| INV-2002 | 215 |  |  | INV-2003 | 880 |
| INV-2003 | 880 |  |  | INV-2005 | 95 |
| INV-2004 | 130 |  |  | INV-2002 | 215 |
| INV-2005 | 95 |  |  | INV-2007 | 410 |
| INV-2006 | 760 |  |  |  |  |
| INV-2007 | 410 |  |  |  |  |

Formula in C2, filled to C8: `=IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")`

Result: Matched; Matched; Matched; Not in bank; Matched; Not in bank; Matched

## How it works

`COUNTIF` counts how many times the invoice appears in the other list. Zero means it is missing, so the IF returns your message. Run the same formula the other way round, with the bank list on the left, to find items the ledger does not have.

## Other ways

- `=IF(ISNA(MATCH(A2,$E$2:$E$6,0)),"Not in bank","Matched")`: MATCH returns #N/A when the value is missing.
- `=IF(ISNUMBER(MATCH(A2,$E$2:$E$6,0)),"Matched","Not in bank")`: The same test written the other way round.

## Common mistakes

- A trailing space, such as "INV-2001 ", is not a match. Clean the lists with TRIM first.
- Matching on the invoice number does not check the amount. For a full reconciliation, compare both.

## Questions

**How do I match on invoice and amount together?**

Use `COUNTIFS($E$2:$E$6,A2,$F$2:$F$6,B2)`, which needs both to match.

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

Source: https://spreadsheetpractice.com/formulas/find-invoices-in-one-list-but-not-the-other
