title: Excel 2013, multiple search criteria to back fill empty fields, large database
body: <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">item</th>
  <th style="text-align: center;">countDate</th>
  <th style="text-align: center;">shopID</th>
  <th style="text-align: center;">lineNumber</th>
  <th style="text-align: center;">defectNumber</th>
  <th style="text-align: center;">jobID</th>
  <th style="text-align: right;">jobID2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">123A1234-123</td>
  <td style="text-align: center;">1/1/2022</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;">1234</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;"></td>
  <td style="text-align: right;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">123A1234-123</td>
  <td style="text-align: center;">1/2/2022</td>
  <td style="text-align: center;"></td>
  <td style="text-align: center;">1234</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;"></td>
  <td style="text-align: right;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">123A1234-123-AB123</td>
  <td style="text-align: center;">1/1/2022</td>
  <td style="text-align: center;">AB12C</td>
  <td style="text-align: center;">1234</td>
  <td style="text-align: center;">0</td>
  <td style="text-align: center;">IP-0000123456</td>
  <td style="text-align: right;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">IP-000ABC0123</td>
  <td style="text-align: center;">1/1/2022</td>
  <td style="text-align: center;">AB12C</td>
  <td style="text-align: center;">1234</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">IP-000ABC0123</td>
  <td style="text-align: right;"></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Above is an example of the type of data I usually get, limited to only those fields that are relevant.</p>
  <p>As you can see there are three instances of item 123A1234-123, 2 of which have no Job ID. I am trying to find a way to fill those blank spaces with the Job ID from the defect number 0 instance of the item.</p>
  <p>The problem I am encountering is that the item code is longer on that instance. There is also the fact that listings in the item field are of several different formats, though they are <em>mostly</em> uniform in in format for the ones I am trying to fix.</p>
  <p>Previous attempts at building an equation to fill the new column jobID2 result in value errors.</p>
  <pre><code>[@jobID2] = INDEX([jobID],MATCH(1,[countDate]=[@countDate])*([lineNumber]=[@lineNumber])*([item]=LEFT([@item],SEARCH(CHAR(164), SUBSTITUTE([@item],&quot;-&quot;,CHAR(164),2))-1)),0))
  </code></pre>
  <p>This equation was written before I found an example in the data that didn't share the same count date.</p>
  <p>I had help writing this but that person never replied to my follow up questions.</p>
  <p>From what I can work out it is supposed to replace the second - with a special character and then search for that special character before returning the jobID. I think the problem is partially that not all the data matches this format, but I'm not that versed in using LEFT(SEARCH(</p>
  <p>I am running Excel 2013 on a (8gb ram, windows 10 enterprise 64 bit, 3.1GHZ i5) machine. I've killed it a couple of times trying to run big equations on some of the data sheets so I'm unsure how complex the equation or script can be.</p>
  <p>Any help I can get is very much appreciated.</p>
  <p>Edit:</p>
  <p>Current state of the equation, returns N/A on all data entries.</p>
  <pre><code>[@jobID2] =INDEX([jobID],MATCH(1,(([@[defectNumber]] = 0)*([lineNumber]=[@lineNumber])*(LEFT([@item],12)=LEFT([item],12))),0))
  </code></pre>
  <p>The problem I am seeing now is that</p>
  <ol>
  <li>it doesn't return a row number, match just spits out 1 aka true</li>
  <li>it doesn't look like each row is searching the whole database, just its own row</li>
  </ol>
  <p>Looked up a different formula</p>
  <pre><code>[@jobID2] = VLOOKUP(1,CHOOSE({1,2},(LEFT([@item],12)=LEFT([item],12))*([@lineNumber]=[lineNumber])*(&quot;&quot;&lt;&gt;[shopID]),[shopID]),2,0)
  </code></pre>
  <p>This returns n/a for all entries <em>except</em> for the ones where shopID &lt;&gt; 0 where it returns a 0</p>
  <p>So... progress</p>