How to Check Duplicate Invoice Numbers Across Worksheets in WPS Office Spreadsheets
Question details
The user needs to check whether a newly entered invoice number already exists on earlier monthly worksheets before recording it.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user maintains monthly invoice worksheets and wants to avoid entering the same invoice number in a new month.
- Observed behavior
- The goal is to quickly compare the new invoice number against previous monthly worksheet ranges and receive a clear match or no-match result.
Make sure the invoice number you want to check is entered in a known cell and that the previous monthly worksheets use a consistent column for invoice numbers.
Use VLOOKUP to check a new invoice number against one worksheet
Use this when you only need to compare the new invoice number with a single previous month sheet.
Open WPS Office Spreadsheets, go to the worksheet where the new invoice number is entered, and click the empty cell where you want the check result.
Type =IFERROR(VLOOKUP(A2,Sheet1!A:A,1,FALSE),"No Match") in the formula bar, replacing A2 with the cell containing the new invoice number and Sheet1!A:A with the previous month's invoice number column.
Press Enter to display the existing invoice number if it is found, or "No Match" when it has not been used before.
Check a new invoice number against multiple monthly worksheets with COUNTIF
Use this when your invoice history is split across several monthly worksheets and you need one total match count.
Check Duplicate Invoice Numbers in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build a formula-based duplicate check across your monthly invoice worksheets. Enter the new invoice number, reference the previous monthly ranges, and WPS Spreadsheets returns a match count or "No Match" result immediately—no separate workbook or complicated macro required.
- 1. Open your invoice workbook: Launch WPS Office and open the existing Microsoft Excel workbook (.xlsx or .xls) that contains your monthly invoice worksheets.
- 2. Enter the new invoice number: Go to the current month's worksheet and type or select the new invoice number in the cell you want to check, such as A2.
- 3. Insert the duplicate-check formula: Click the result cell, type =COUNTIF(Jan!A:A,A2)+COUNTIF(Feb!A:A,A2)+COUNTIF(Mar!A:A,A2), and press Enter.
- 4. Review the result: If the result is 0, the invoice number is new; if it is 1 or more, the number already exists in a previous monthly worksheet.
Frequently Asked Questions
Can COUNTIF check all worksheets at once with a 3D reference in WPS Office?
COUNTIF does not support a 3D reference such as Jan:Mar!A:A. Add a COUNTIF formula for each monthly sheet, or use a consolidated invoice table so one COUNTIF can check the entire invoice history.
Which formula returns "No Match" instead of an error when an invoice number is not found?
Wrap VLOOKUP in IFERROR. For example, =IFERROR(VLOOKUP(A2,Sheet1!A:A,1,FALSE),"No Match") displays "No Match" when the invoice number is not found on the referenced sheet.
What is the better way to manage duplicate invoice checks if I have many monthly worksheets?
Store all invoices in a single table with columns for invoice number, date, payment status, and other fields. Then use COUNTIF, VLOOKUP, filters, or a PivotTable to check duplicates and analyze unpaid invoices without copying and deleting monthly worksheets.
Will these formulas work when I open my Excel invoice workbook in WPS Office?
Yes. WPS Office Spreadsheets supports the commonly used VLOOKUP and COUNTIF functions and opens .xlsx, .xls, and .csv files, so you can continue working with existing Excel workbooks.
Can I highlight duplicate invoice numbers automatically?
You can use conditional formatting with a COUNTIF-based rule to highlight a cell when the invoice number already appears in a previous month range, then apply the same rule to the invoice number column.
