Spreadsheet Practice

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 to C8
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"

  1. $E$2:$E$65 rows × 1 col: "INV-2001", "INV-2003", "INV-2005", "INV-2002", …

    A range: 5 cells in one column.

  2. A2"INV-2001"

    The value in A2.

  3. COUNTIF($E$2:$E$6,A2)1

    COUNTIF: Counts cells that meet one condition.

    • range$E$2:$E$65 rows × 1 col: "INV-2001", "INV-2003", "INV-2005", "INV-2002", …
    • criteriaA2"INV-2001"
  4. COUNTIF($E$2:$E$6,A2)=0FALSE

    The = operator tests whether the two sides are equal.

  5. 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_testCOUNTIF($E$2:$E$6,A2)=0FALSE
    • value_if_true"Not in bank"not used
    • value_if_false"Matched"

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.

FormulaResultWhen 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

In 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-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.