title: Extract substring of list based on another list
body: <p>Using two lists, one consisting of names with added information in various forms (see below for example - list 1) and one consisting of the clear formatted names, i.e. with no added information (list 2)</p>
  
  <pre><code>List 1
  --------
  Netto City      | Value
  Imerco City     | value 
  Bilka Suburb    | value
  Bauhaus, City   | Value
  City FDB Superb | Value
  
  List 2
  ------
  Netto
  Imerco
  Bilka
  Bauhaus
  FDB Super
  </code></pre>
  
  <p>What I am trying to do is create a filter, so that no matter what the first column of my source data(list 1) looks like, i will be able to sum the values based on (list 2).  </p>
  
  <p>Something similar to this: <a href="https://stackoverflow.com/questions/1018335/excel-extracting-data-based-on-another-list">Excel - extracting data based on another list</a></p>
  
  <p>I tried using vlookup, but that does not search for substrings, then i tried using </p>
  
  <pre><code>=IF(COUNTIF(A$4:A$9;"*"&amp;D5&amp;"*")&gt;0;
      INDIRECT(ADDRESS(MATCH("*"&amp;D5&amp;"*";A$4:A$9;0);4));"not found")
  </code></pre>
  
  <p>But that appears to do the opposite, search list 1 for a single cell value from list 2. 
  I can't quite get my head around if this works just as well, I havent been able to get it to work anyway, thus my search for the other way. Search List 2, for each item from List 1.</p>
  
  <p>But, ultimately, what I am trying to accomplish is to create a list from the source data, which I can use to categorize each item in list 1 from, based on list 3</p>
  
  <pre><code>List 3
  Bilka     | Cat1
  Imerco    | Cat2
  FDB Super | Cat1
  etc. 
  </code></pre>
  
  <p>For that to work, i need a clean list of the source data, without all the extra information which comes with it. </p>
  
  <p>I use the following sumif</p>
  
  <pre><code>=SUMIFS($F$3:$F$703;$B$3:$B$703;
      "="&amp;$H4;$D$3:$D$703;"&gt;="&amp;I$2;$D$3:$D$703;"&lt;="&amp;I$3)
  </code></pre>
  
  <p>to sum all sums belonging to a particular item in List 3 (where i've manually created List 3), between to dates.</p>
  
  <p>The purpose of this is to create a sheet that contains all expenditures to a particular store or category of ones own choosing, for instance the ones listed in List 1, are primarily food stores. </p>
  
  <p>Edit - Clarification.</p>
  
  <p>What I am proposing to do is a multistage process.
  Stage 1:
  Insert original source data (done)</p>
  
  <p>Stage 2:
  Filter source data for unique values (done)</p>
  
  <p>Stage 3: 
  Create list of approve names for each item in source data
   - Ie, Bilka Suburb into Bilka, Netto City into Netto
  Here 'Netto' and 'Bilka' are approved names which is manually created to allow for grouping in stage 4.  I am looking to automatize this step.</p>
  
  <p>Stage 4:
  Group each item from the list of Stage 3, based on name and date-interval, weekly monthly whatever (done) if i could only get Stage 3 to work, as it works on my manually corrected data.</p>
  
  <p>Stage 5:
  Select appropriate category, and type for each item in resulting list from Stage 3:
  Bilka, is a food place, so it would get the category 'food', same as netto, where Bauhaus would get the category 'Building Supplies', each of these items would get the type 'expense' where say wage would get the type 'income' (done)  </p>
  
  <p>the solution to stage 5, is just a vlookup, based on the category into a table that lists each category with a type, so that is simple enough. </p>
  
  <p>Final Solution: Requires that the list to iterate over is in column G, and outputs the list of approved names in column H. There is the error of if not being able to know the difference between an item such as "Super" and "SU", I don't know how to fix that. If anyone has any suggestions on that I am all ears.</p>
  
  <pre><code>Sub LoopCells()
  Sheets("RawData").Select
  Sheets("RawData").Activate
  LRApproved = Cells(Rows.Count, "H").End(xlUp).Row
  LRsource = Cells(Rows.Count, "G").End(xlUp).Row
  For Each approvedcell In Worksheets("RawData").Range("H2:H" &amp; LRApproved).Cells     'Approved stores entered by users
  For Each sourcecell In Worksheets("RawData").Range("G2:G" &amp; LRsource).Cells 'items found from bank statement export
      If InStr(UCase(sourcecell.Value), UCase(approvedcell.Value)) &lt;&gt; 0 Then
                  sourcecell.Offset(0, 2).Value = approvedcell.Value
      End If
     Next sourcecell
  Next approvedcell
  End Sub
  </code></pre>
  
  <p>Thanks for all the help.
  Edit: Added final solution and VBA tag.</p>