Forum Discussion

Copper Contributor

Jul 31, 2025

Solved

Auto-numbering for invoices

I used Xero for my invoicing and when i upload my spreadsheet to Xero i need to manually input the invoice numbers. Is there a way that i can use a formula to automat this process and save me tim...

m_tarler
Jul 31, 2025
try
="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) ), "0000")
if it isn't formatted as a Table then replace [@Company] with A2 and [Company] with A2:.A9999

Bronze Contributor

Jul 31, 2025

try

="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) ), "0000")

if it isn't formatted as a Table then replace [@Company] with A2 and [Company] with A2:.A9999

robbie_no11
Copper Contributor
Aug 01, 2025
That works great thank you.

The only thing i need to know now is how do i continue from my last invoice number. IE from INV-026302
- m_tarler
  Bronze Contributor
  Aug 01, 2025
  if it is a 'clean' start then:
  ="INV-"&TEXT(XMATCH( [@Company], UNIQUE( [Company] ) )+26302, "000000")
  but if you still have the other companies in the list then you need to account for that and it will depend on some scenarios:
  a) lines A1-A100000 are the 'old' data with companies that are no longer used and the last invoice is INV-026302. so going forware starting on line 100001 should be only new companies names and new invoice numbers starting after 026302. then:
  ="INV-"&TEXT(XMATCH( A100001:.A1048576 , UNIQUE( A100001:.A1048576 ) )+26302, "000000")
  b) lines A1-A100000 are the 'old' data with companies (and invoices are already entered in column B) but those same companies may show up again and should use the prior Invoice number then you could use:
  =XLOOKUP( A100001, A$1:A100000, B$1:B100000, "INV-"& 1+MAX(--TEXTAFTER(B$1:B100000, "-")))
  And then copy and paste down as needed

Resources