title: Number increment in Google Sheets formula
body: <p>In a Google Sheets database, I have a formula which I have built in order to allocate a reference number to a series of companies.</p>
  
  <p>Each company should have its unique number in the form of <code>RET00XX</code> where XX will represent the unique company number. I would like these numbers to be sequential, starting on 1 and going on +1 after that.
  Whenever a new company is inserted in the database, the formula should be able to attribute it a reference number. It should also be able to verify if the company already exists in the database and, if so, automatically attribute it the company's unique reference number, instead of creating a new one.</p>
  
  <p>The company names are in cells of column B.</p>
  
  <p>This is the formula I have built (an example of the one in row 2):</p>
  
  <pre><code>=ARRAYFORMULA(IF($B2&lt;&gt;"",IF((COUNTIF($B$1:$B1,$B2)&gt;0),INDEX($A$1:$R2,MATCH($B2,$B$1:$B1,0),12),CONCATENATE("RET00",ROW($B2))),""))
  </code></pre>
  
  <p>The steps it takes are:<br></p>
  
  <ol>
  <li>It verifies that column B in the correspondent row is not empty;</li>
  <li>With the <code>COUNTIF</code> function, verifies that the company does not exist in any of the previous rows;</li>
  <li>If the company does exist, it attributes the correspondent reference number through the <code>INDEX</code> function;</li>
  <li>If the company doesn't exist, it attributes the company a new reference number with the <code>CONCATENATE</code> and <code>ROW</code> functions.</li>
  </ol>
  
  <p>The formula is largely working, although there are some problems.
  Users adding to this database have the habit of adding entries by inserting rows in the middle of the database. This makes it so, due to the way the formula is built, that company unique reference codes change each time that happens. I believe this is partially due to the fact that I use a <code>ROW</code> function. <br> Also, given that new rows are inserted in the middle of the database, the formula should be able to verify is the company already exists not only by looping through all previous rows but rather through all rows (if a new row is inserted, the formula will only verify previous rows, when the company could be in the rows after the new one).</p>
  
  <p>How can I attribute sequential numbers in a formula without reference to <code>ROW</code>? Also, how can I make sure that the spreadsheet verifies for all rows of column B instead of just the ones before the inserted row?</p>