EXCEL AND LISTS
Find missing records in Excel: see exactly what is missing from which list
Your ERP system shows 1,184 invoices for October, your accounts show 1,176. So eight are missing. But which ones? You put two exports side by side, go through them number by number, and still end up with the nagging feeling that you missed one.

That costs more than an afternoon. A sales invoice that was never posted is missing from the VAT return and from the open items list. It often surfaces weeks later, when fixing it takes far more work.
This guide shows how to find missing records in Excel in both directions, with a result you can rely on. You get three methods for different Excel versions, a way to find gaps in invoice numbers and an example that shows why the row count alone can mislead you.
The short answer: how do you find missing records in Excel?
A record is missing when its key appears in list A but not in list B. The key is the column that identifies an entry uniquely, such as an invoice, customer or item number. For each row, you count how often its key appears in the other list. If the count is 0, the record is missing.
- Decide the direction
Do you want to know what is missing from B, what is missing from A, or both? Usually you need both directions.
- Clean the key
Remove stray spaces and give the key column the same format in both lists, otherwise identical numbers count as different.
- Count occurrences
A helper column with COUNTIF counts how often each key appears in the other list.
- Filter for 0
Every row with 0 is missing from the other list. That is your checklist.
- Check the other direction
Repeat the step the other way round. Only both results together fully explain the difference.
What is missing where? Ask the right question first
| Question | Example | Method |
|---|---|---|
| What is in A but not in B? | Invoices in the ERP system that were never posted | COUNTIF or Left Anti join |
| What is in B but not in A? | Postings without an invoice in the ERP system | COUNTIF reversed or Right Anti join |
| What is missing on either side? | Stock count sheet against inventory records | both directions or a comparison tool |
| Which numbers are missing from a sequence? | Gaps in invoice numbers | generate the full sequence and compare |
How do you find missing values with COUNTIF?
COUNTIF exists in every Excel version, so you do not need an upgrade. Turn both lists into tables with Ctrl+T and name them “ERP” and “Accounts”. In a new column of the ERP table, enter: =COUNTIF(Accounts[InvoiceNo],[@InvoiceNo])=0
TRUE means this invoice is missing from the accounts. Filter the column for TRUE and you have your checklist, instead of comparing 1,184 rows by eye. For the other direction, put the same formula into the Accounts table and swap the table names.
How do you highlight missing values?
If you prefer to see the rows in colour, use conditional formatting. It does not accept table references, so use sheet references here. Assuming the numbers sit in column A on both sheets: select the numbers in list A from A2, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =COUNTIF(Accounts!$A:$A,$A2)=0. For a checklist you work through, a filter is more practical.
How do you list missing records automatically?
In Microsoft 365, Excel 2021 and Excel 2024, FILTER returns all missing records at once, without a helper column: =FILTER(ERP[InvoiceNo],COUNTIF(Accounts[InvoiceNo],ERP[InvoiceNo])=0,"nothing missing"). The list updates as soon as the data changes. The last part, “nothing missing”, matters: without it, Excel shows a #CALC! error when everything is actually complete.
An alternative without wildcards: ISNA with XLOOKUP
XLOOKUP returns the #N/A error when it finds nothing. ISNA turns that into TRUE or FALSE: =ISNA(XLOOKUP([@InvoiceNo],Accounts[InvoiceNo],Accounts[InvoiceNo])). TRUE again means missing. The advantage: XLOOKUP looks for an exact match by default and only treats * and ? as wildcards in match mode 2, so special characters in the key are taken literally. XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024. In Excel 2016 and 2019, =ISNA(MATCH([@InvoiceNo],Accounts[InvoiceNo],0)) works the same way, except that MATCH, like COUNTIF, reads * and ? as wildcards.
Power Query: find missing records every month with an anti join
If the check comes back every month, Power Query pays off. You set up the comparison once and refresh it with one click from then on. An anti join is a merge that returns only the rows without a counterpart. That is exactly a list of missing records.
- Load both lists
Click into each table and choose Data > From Table/Range. Set the key column's data type to Text, remove stray spaces with Transform > Format > Trim, and save with Close & Load To > Only Create Connection.
- Merge
Choose Data > Get Data > Combine Queries > Merge, or Home > Merge Queries in the Power Query Editor. On top, the list whose missing records you want. Below, the list you compare against.
- Pick key and join kind
Select the key column in both tables. As join kind, choose Left Anti, which keeps only rows from the first table that have no match in the second. The bottom of the dialog shows how many rows that is.
- Tidy up and load
Remove the empty merged column and load the result to its own sheet. For the other direction, create a second query with Right Anti.
Unlike COUNTIF, Power Query is case sensitive when it merges. “re-2026-1437” and “RE-2026-1437” count as different keys. If your programs export the same number in different cases, convert the keys to upper case first.
Next month, replace the data and click Data > Refresh All. The checklist is ready in seconds, and it follows the same rules every time, no matter who on the team refreshes it. If you want to pull values from the second list rather than only look for gaps, see how to compare two Excel lists.

How do you find missing invoice numbers in Excel?
Sometimes there is no second list. You only want to know which numbers are missing from a sequence. In Germany § 14(4) sentence 1 no. 4 UStG and in Austria § 11(1) no. 3 lit. h UStG 1994 generally require a sequential invoice number that is assigned only once to identify the invoice. The wording of the law does not demand a sequence without gaps. For Germany, the VAT application decree (UStAE, section 14.5 para. 10) states explicitly that a gapless sequence is not mandatory. In an audit, however, the tax office may ask about gaps. You are on safe ground if you know the reason for every missing number, such as a discarded draft. Your tax adviser can tell you how best to document this.
In Microsoft 365, Excel 2021 and Excel 2024, SEQUENCE creates the full series and FILTER shows what is missing: =LET(n,Numbers[No],all,SEQUENCE(MAX(n)-MIN(n)+1,,MIN(n)),FILTER(all,COUNTIF(n,all)=0,"no gap")). The formula checks the range between the lowest and the highest number present.
- Older Excel versions: sort the numbers in ascending order and enter =A3-A2 in B3. Every value above 1 marks a gap just before that row, and a 0 marks a number used twice.
- Numbers with a prefix: for numbers like RE-2026-0412, extract the numeric part, for example with =VALUE(RIGHT([@InvoiceNo],4)), and check each number range separately.
- Year change: if numbering restarts every year, check each year on its own. Otherwise the numbers of both years mix, and a real gap in one year is hidden by the same number from the other year.
An example from the month end close: why 8 missing invoices are 14 cases
The example shows the key point: the row count alone tells you little. Records missing on both sides cancel each other out, and here a difference of 8 hides 14 real cases. Anyone who only looks for eight posts three invoices too few and still considers the close done. The free Excel comparison shows both directions in one pass, with no formula and no upload.
| Invoice number | ERP system | Accounts | Finding |
|---|---|---|---|
| RE-2026-1412 | yes | yes | fine |
| RE-2026-1437 | yes | no | export interrupted, post it |
| RE-2026-1466 | yes | only with a trailing space | looks missing, fine after TRIM |
| AR-2026-007 | no | yes | down payment invoice created outside the ERP system |
Common mistakes when looking for missing records
- Checking one direction only: you see what is missing from B, but not what B has in addition. Always check both directions.
- Counting rows only: equal row counts do not prove that both lists contain the same records.
- Messy keys: trailing spaces and non breaking spaces from web exports create gaps that are not real. TRIM alone does not remove non breaking spaces, so use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). Numbers stored as text in one list cause false gaps mainly with XLOOKUP, MATCH and Power Query.
- Different periods: one export runs to 31 October, the other to the export date. Agree on the period before you compare.
- Overlooking duplicate keys: if a number appears twice in B, it is not missing, but it is still a problem. See how to find duplicates in Excel.
- A checklist without status: note the finding and whether it is resolved for every missing row. Otherwise you check the same cases again next month.
Limits and effort: when manual checking stops paying off
The formula takes minutes. Everything around it takes time: pulling exports, aligning formats, sorting out false gaps, resolving findings. A cautious assumption is 2 hours per month end. That is 24 hours a year. At an assumed internal cost of €38 per hour, meaning wages plus overheads, the check costs about €910 a year. On top comes what a missed invoice costs: if it is missing from the VAT return, correcting it later takes more effort than checking today.
Run your own figures with the office work cost calculator. It gets hard when more than two sources are involved, when keys differ between programs, or when the missing records then have to be typed into another program by hand. And if only one colleague knows how the check works, it stops the moment she goes on holiday.
When an office helper makes sense
For a quick check without formulas, use our free Excel comparison. Choose, for example, the ERP export as the previous file and the accounts export as the new file. “Missing” then means only in the ERP system, and “New” means only in the accounts, so you see both directions at once. The column you match on needs the same heading in both files, such as Invoice number. The tool suggests a column with unique values by itself, and you can change it. By default it ignores spaces at the start and end of a value, and it can ignore upper and lower case if you tick the option. Duplicate numbers are flagged as unclear. You can download the result as a list, and your files stay in your browser.
If the check between ERP system and accounts comes back every month, an office helper, a small tool we build for exactly this kind of task, can fetch the exports, clean the keys and produce a checklist with findings. Your team member then only resolves the real cases, and the check still runs when she is out of the office. Your existing programs stay as they are. The page on automating Excel work shows what such a workflow looks like.
If your month end check pulls from several programs, or depends on one person, an outside view helps. In the free workflow check, describe which lists you compare and how often. Within 1–2 working days you get a personal, free assessment of whether the formula from this article is enough or an office helper should take over the check, at a fixed price from €1,990 excl. VAT and, for small projects, in place 3–7 days after the use case is agreed.
