To find duplicate invoices in Excel, add a helper column that cleans each invoice number, then use COUNTIFS on vendor, cleaned invoice number and amount: =COUNTIFS($A:$A,A2,$F:$F,F2,$D:$D,D2)>1. TRUE marks a likely duplicate. A second COUNTIFS with a date window finds near duplicates that carry different invoice numbers.
This guide walks through that method on a standard accounts payable export. It assumes five columns, in this order:
| Column | Field |
|---|---|
| A | Vendor |
| B | Invoice number |
| C | Invoice date |
| D | Amount |
| E | Payment date |
Columns F onward hold the helper formulas. Every formula starts in row 2 and gets filled down.
How do you prepare an AP export for a duplicate check?
Most missed duplicates come from formatting. “INV-00482” and “482” are the same invoice to a person and two different values to Excel. Clean the data before you count anything.
- Export paid and open invoices from your accounting system as CSV or XLSX. Cover at least 12 months so that a resubmitted invoice from last quarter still has something to match against.
- Check that Invoice date and Amount are real dates and numbers. If they are left aligned, Excel is reading them as text. Select the column and use Data > Text to Columns > Finish to convert them. Date formulas later in this guide will not work on text dates.
- Remove stray spaces from vendor names with a helper column,
=TRIM(A2), then paste the results back over column A as values. - Add column F, headed Key, with one of the formulas below.
If you have Excel for Microsoft 365, REGEXREPLACE does the cleanup in one short formula. According to Microsoft’s REGEXREPLACE documentation, it is available in Excel for Microsoft 365 on Windows and Mac.
=REGEXREPLACE(REGEXREPLACE(UPPER(TRIM(B2)),"[^A-Z0-9]",""),"^(INV)?0*","")
The inner call removes everything that is not a letter or digit (hyphens, spaces, slashes, dots, the # sign). The outer call strips a leading “INV” and any leading zeros. “INV-00482”, “inv 482” and “00482” all become “482”.
In Excel 2021 or 2024, which have LET but not REGEXREPLACE, use this version:
=LET(s,UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"-","")," ",""),"/",""),".","")),c,IF(LEFT(s,3)="INV",MID(s,4,50),s),MID(c,FIND(LEFT(SUBSTITUTE(c,"0","")),c),50))
The first part removes hyphens, spaces, slashes and dots and converts to upper case. The second drops a leading “INV”. The last part finds the first character that is not a zero and returns everything from there, so leading zeros go and zeros inside the number stay. “20931” stays “20931”.
In Excel 2016 or 2019, split the same logic across three helper columns. Put the UPPER and SUBSTITUTE part in the first, strip the prefix in the second with =IF(LEFT(F2,3)="INV",MID(F2,4,50),F2), and strip leading zeros in the third with =MID(G2,FIND(LEFT(SUBSTITUTE(G2,"0","")),G2),50). Use the third column as the key and shift the column letters in the later formulas to match.
Look at the keys your data produces before going further. If your vendors use other prefixes, such as “BILL” or a two letter branch code, add them to the pattern.
Is there an Excel formula to find duplicates?
Yes. COUNTIFS is the one to use for invoices because it can check several columns at once. It counts the rows that match every condition you give it, and Microsoft’s COUNTIFS reference allows up to 127 range and criteria pairs.
How do you flag exact duplicate invoices?
In column G, headed Exact:
=COUNTIFS($A:$A,A2,$F:$F,F2,$D:$D,D2)>1
The formula counts rows with the same vendor, the same cleaned key and the same amount. The row always matches itself, so a count above 1 means at least one other row matches too. Filter column G for TRUE and you have your list of exact duplicates.
For a quick visual check without formulas, select the Key column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This only looks at one column, so two vendors who both issue invoice “1001” will light up. Use it to scan, and rely on COUNTIFS for the actual list.
Two cautions. COUNTIFS ignores upper and lower case, which helps with vendor names. It also treats * and ? in a criterion as wildcards, so a vendor name containing an asterisk can match more rows than you expect.
How do you find near duplicate invoices in Excel?
Exact matching misses the harder cases, where the second copy has a new invoice number, a different vendor spelling or a typo in the amount. Each of these needs its own column. For the wider picture of how these cases happen, see why duplicate payments happen.
Same vendor, same amount, within a few days
In column H, headed Near:
=COUNTIFS($A:$A,A2,$D:$D,D2,$C:$C,">="&(C2-7),$C:$C,"<="&(C2+7),$F:$F,"<>"&F2)>0
This flags a row when the same vendor has another invoice for the same amount dated within 7 days either side, under a different invoice number. The "<>"&F2 condition leaves out the rows already caught by the exact check. Change 7 to suit how often your vendors bill. Weekly service vendors will need a smaller window or they will flag every week.
Same amount, different vendor spelling
When “Pine Street Cleaning” and “Pine St. Cleaning” both exist in the vendor list, the checks above treat them as two vendors. Column I compares all invoices for the same amount in the window against those from the same vendor:
=COUNTIFS($D:$D,D2,$C:$C,">="&(C2-7),$C:$C,"<="&(C2+7))>COUNTIFS($A:$A,A2,$D:$D,D2,$C:$C,">="&(C2-7),$C:$C,"<="&(C2+7))
TRUE means some other vendor name has the same amount within 7 days. Expect noise from round amounts like rent, retainers and subscriptions. Treat this column as a list to read through. A vendor master cleanup does more good here than any formula.
Transposed digits
A clerk who keys 1,250.00 as 1,520.00 creates an invoice that no equality check will match. There is an old bookkeeping test for this. If two numbers use the same digits in a different order, their difference is divisible by 9. The reason is that every place value (1, 10, 100 and so on) leaves a remainder of 1 when divided by 9, so any number has the same remainder as the sum of its digits. Rearranging the digits does not change that sum, so both numbers have the same remainder, and their difference is a multiple of 9. Here, 1,520 minus 1,250 is 270, which is 9 times 30.
The test only works in one direction. A difference divisible by 9 does not prove a transposition, since about one in nine unrelated differences passes too. Use it only inside a single vendor. After sorting by vendor and then amount, this checks each row against the one above it:
=AND(A3=A2,D3<>D2,MOD(ROUND((D3-D2)*100,0),9)=0)
The *100 works in cents, so 12.50 and 15.20 are compared as 1250 and 1520.
In Excel 2021, 2024 or Microsoft 365 you can test for the same digits directly. Column J builds a signature by sorting the digits of the amount in cents. The “S” in front keeps COUNTIFS from reading the signature as a number:
=LET(t,TEXT(ROUND(D2*100,0),"0"),"S"&CONCAT(SORT(MID(t,SEQUENCE(LEN(t)),1))))
Then column K, headed Transposed:
=COUNTIFS($A:$A,A2,$J:$J,J2,$D:$D,"<>"&D2)>0
TRUE means the same vendor has another invoice whose amount uses exactly the same digits in a different order.
Can I use VLOOKUP to find duplicates?
Yes, when you are comparing two lists. COUNTIFS works best inside one list. A lookup is the natural tool for the monthly question AP actually asks: is anything in this batch already in the payment history?
Put this month’s invoices on a sheet named New and your payment history on a sheet named Paid, both with the same columns and the same Key formula in column F. On the New sheet:
=IFERROR(VLOOKUP(F2,Paid!$F:$F,1,FALSE),"Not found")
This returns the key when the invoice number already exists in the history. VLOOKUP matches on one column and stops at the first hit, so it cannot tell you whether the vendor also matches.
XLOOKUP can match on vendor and key together and return the payment date. Microsoft’s XLOOKUP page lists it for Microsoft 365, Excel 2021 and Excel 2024, and not for Excel 2016 or 2019.
=XLOOKUP(1,(Paid!$A$2:$A$20000=A2)*(Paid!$F$2:$F$20000=F2),Paid!$E$2:$E$20000,"Not paid")
The two comparisons each produce TRUE or FALSE for every row, and multiplying them gives 1 only where both match. The formula returns that row’s payment date, or “Not paid” when there is no match. Use fixed ranges here rather than whole columns, which slow this kind of formula down. Format the result column as a date.
The COUNTIFS equivalent is =COUNTIFS(Paid!$A:$A,A2,Paid!$F:$F,F2)>0, which gives TRUE or FALSE without the date.
What does the flagged sheet look like?
Here is a small example with the helper columns filled in. Payment date is left out to save space, and the signature column J is hidden.
| Row | Vendor | Invoice no. | Invoice date | Amount | Key (F) | Exact (G) | Near (H) | Other vendor (I) | Transposed (K) |
|---|---|---|---|---|---|---|---|---|---|
| 2 | Northside Supply | INV-00482 | 2026-08-03 | 1,250.00 | 482 | TRUE | FALSE | FALSE | FALSE |
| 3 | Northside Supply | 482 | 2026-08-03 | 1,250.00 | 482 | TRUE | FALSE | FALSE | FALSE |
| 4 | Harbor Electric | 7731 | 2026-08-10 | 3,480.00 | 7731 | FALSE | TRUE | FALSE | FALSE |
| 5 | Harbor Electric | 7732 | 2026-08-14 | 3,480.00 | 7732 | FALSE | TRUE | FALSE | FALSE |
| 6 | Pine Street Cleaning | PS-118 | 2026-08-12 | 615.00 | PS118 | FALSE | FALSE | TRUE | FALSE |
| 7 | Pine St. Cleaning | PS 118 | 2026-08-12 | 615.00 | PS118 | FALSE | FALSE | TRUE | FALSE |
| 8 | Metro Office Co | 20931 | 2026-08-18 | 1,520.00 | 20931 | FALSE | FALSE | FALSE | TRUE |
| 9 | Metro Office Co | 20937 | 2026-08-21 | 1,250.00 | 20937 | FALSE | FALSE | FALSE | TRUE |
Rows 2 and 3 are the same invoice keyed two ways. Rows 4 and 5 may be two real deliveries or one bill sent twice with a new number (the usual form of double billing), and only the documents will tell you which. Rows 6 and 7 are one invoice under two vendor records, which the exact check misses because the vendor names differ. Rows 8 and 9 share digits, which is worth a look at the original invoice.
Every TRUE is a question for a person. Pull both documents, compare line items and purchase order numbers, and ask the vendor if it is still unclear. If the two documents disagree on quantity or price instead, treat it as an invoice discrepancy and resolve it with the vendor before anything is paid. Save the workbook with the formulas in place and it becomes your duplicate payments template: paste the next export into columns A to E and the flags recalculate.
Where does the spreadsheet method break down?
The formulas work, but the process around them has limits:
- Someone has to export, clean and review the sheet each time, so it tends to slip in busy months.
- It checks a snapshot. An invoice that arrives the day after the export waits until the next run.
- Vendor spelling differences produce noise, and the formulas cannot tell which spelling is correct.
- It only sees what is in the export. Invoices still sitting in an inbox as PDFs are not in it.
- When the export is taken from paid invoices, the check happens after the money has gone out.
For the last case, a periodic look back through paid invoices is still worth doing. An accounts payable audit covers how to run one.
OverpayAlert runs these comparisons as invoices come in. You forward or upload invoices, and it compares invoice number, vendor, amount and date against every invoice you have sent before, then flags potential duplicates and unusual vendor price increases for someone to review. On the Growth and Scale plans, results can be exported to CSV. The duplicate invoice detection page shows how the comparison works.
If you would rather not rebuild this sheet every month, start a 7-day free trial and forward the next batch of invoices to OverpayAlert.