Continue to WhatsApp?

The chat opens in WhatsApp, in the app or in a new tab. You leave our website, and data such as your IP address is sent to WhatsApp (Meta).

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.

Tall stacks of file folders and paper records with yellow tabs in an office, where single records are hard to find
Photo: Wesley Tingey on Unsplash

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.

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

  2. Clean the key

    Remove stray spaces and give the key column the same format in both lists, otherwise identical numbers count as different.

  3. Count occurrences

    A helper column with COUNTIF counts how often each key appears in the other list.

  4. Filter for 0

    Every row with 0 is missing from the other list. That is your checklist.

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

Typical questions and the matching method
QuestionExampleMethod
What is in A but not in B?Invoices in the ERP system that were never postedCOUNTIF or Left Anti join
What is in B but not in A?Postings without an invoice in the ERP systemCOUNTIF reversed or Right Anti join
What is missing on either side?Stock count sheet against inventory recordsboth directions or a comparison tool
Which numbers are missing from a sequence?Gaps in invoice numbersgenerate 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.

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

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

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

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

A finger runs down a handwritten list with empty check boxes next to a keyboard, entries are ticked off one by one
Photo: Jakub Żerdzicki on Unsplash

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.

Extract from the checklist in the example
Invoice numberERP systemAccountsFinding
RE-2026-1412yesyesfine
RE-2026-1437yesnoexport interrupted, post it
RE-2026-1466yesonly with a trailing spacelooks missing, fine after TRIM
AR-2026-007noyesdown 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.

Sources and further reading

FAQ

Quick answers.

How do I find records in Excel that are missing from another list?

Enter =COUNTIF(ListB,A2)=0 in a helper column, where ListB is the range of the other list, and filter for TRUE. Those rows from list A are missing from list B. Then check the other direction, because only both results together fully explain the difference between the lists.

How do I check in Excel whether a value exists in another list?

=COUNTIF(range,value)>0 returns TRUE if the value appears. In Microsoft 365, Excel 2021 and Excel 2024 you can also use =ISNA(XLOOKUP(value,range,range)), which returns TRUE if the value is missing. In both cases, watch for different formats and extra spaces in the key column.

How can I highlight missing values in Excel?

Use conditional formatting with a formula such as =COUNTIF(Sheet2!$A:$A,$A2)=0. Excel then colours every row whose key does not appear on the other sheet. For a list you work through case by case, a helper column with a filter is usually clearer and easier to track.

How do I find missing numbers in a sequence in Excel?

Sort the numbers in ascending order and calculate the difference to the previous row. Every value above 1 marks a gap. In Microsoft 365, Excel 2021 and Excel 2024, SEQUENCE creates the full series, and FILTER with COUNTIF lists every number that is missing from it.

Are gaps in invoice numbers allowed in Germany and Austria?

In Germany, yes: VAT law requires a sequential number that is assigned only once, and the tax administration states in the VAT application decree that a gapless sequence is not mandatory. Austrian law uses the same wording and does not explicitly demand a gapless sequence either. Gaps should still be explainable, for example through discarded drafts. If in doubt, check with your tax adviser.

LET'S TALK ABOUT YOUR WORKFLOW

Write to us. We reply personally.

A question, an idea or a task that costs time every week? A few sentences are enough. You get an honest answer from the people who also build your office helper.

Ivan, Lukas & JanReply within 1–2 working days

  • Personal reply
  • No obligation
  • Based in Vienna, GDPR aware
Photo of Ivan
Rather talk directly?Ivan, your contact person+43 676 59 40 027office@pebblebyte.comMon to Fri 08:00–18:00
Have a specific task? Get your free workflow check

Your message

Please do not enter passwords or confidential customer data. We only use your details to handle your request. No newsletter, no sharing for advertising. Details in our privacy notice.