How to find invoices that are in one list but not the other in Excel
Formula (Excel and Google Sheets)
=IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")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.
- Result on the example
- one result per row in
C2, filled down toC8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C8. Change any input and the result updates, or edit the formula to experiment.
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.
=IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")Result"Matched"
$E$2:$E$65 rows × 1 col: "INV-2001", "INV-2003", "INV-2005", "INV-2002", …A range: 5 cells in one column.
A2"INV-2001"The value in A2.
COUNTIF($E$2:$E$6,A2)1COUNTIF: Counts cells that meet one condition.- range
$E$2:$E$65 rows × 1 col: "INV-2001", "INV-2003", "INV-2005", "INV-2002", … - criteria
A2"INV-2001"
- range
COUNTIF($E$2:$E$6,A2)=0FALSEThe = operator tests whether the two sides are equal.
IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched")"Matched"IF: Returns one value if a test is true, another if false.- logical_test
COUNTIF($E$2:$E$6,A2)=0FALSE - value_if_true
"Not in bank"not used - value_if_false
"Matched"
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(COUNTIF($E$2:$E$6,A2)=0,"Not in bank","Matched"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(ISNA(MATCH(A2,$E$2:$E$6,0)),"Not in bank","Matched") | Matched, Matched, Matched… | MATCH returns #N/A when the value is missing. |
| =IF(ISNUMBER(MATCH(A2,$E$2:$E$6,0)),"Matched","Not in bank") | Matched, Matched, Matched… | 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.
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 invoices that are in one list but not the other
C2, show Matched when the ledger invoice appears in the bank list (E2:E6) and Not in bank when it does not. Fill down to C8.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.
Checked by running on 2026-11-21: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.