title: Matching values from a range on a different sheet VBA
body: <p>I have two tables, each one on different sheets. The first table is the one to be filled. It contains 4 columns, the first column contains code ie: &quot;10007&quot;, &quot;10009&quot;, the second column has the type of product ie: &quot;H10&quot;, &quot;H12&quot;, etc, and each type of product uses a type of flour,  the third column has the demand of such products and the last column has the consumption, which is the one I am trying to obtained.</p>
  <p>The second table indicates which values on the demand column can be sum to obtained the consumption of each product.
  Example:</p>
  <p>Table 1:</p>
  <pre><code>Week 1   CODE     Flour      Consumption       Product     Demand
  
            1007      GF                            H102       152
            1008      Regular                       H104        450
                                                   H105        256
  </code></pre>
  <p>Table 2</p>
  <pre><code>  Product    Code     Flour
     H102       1007     GF
     H104       1007     GF
     H105       1008     Regular 
  </code></pre>
  <p>So for example, if i am looking for the consumption of 1007 from table 1 then I have to sum the demand if the product matches on the second table. So basically on the second table you find like a guide that it tells you product x is form by code x and flour x, so when you go to the first week you base the consumption on how it matches to the guide. Therefore, the first consumption should be 152+450 since H102 and H104 have the same code, and the second consumption should be just 256.</p>
  <p>I was using range to see if the code will try to match the value on table 1 to table 2.</p>
  <pre><code>    Dim i As Integer
  
     For i = 14 To 76
  
  
  If Cells(i, 23).Value = Worksheets(&quot;Products&quot;).Cells(15, 8).Value And Cells(i, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;).Value And Cells(i + 1, 29).Value &lt;&gt; Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;).Value And Cells(i + 2, 29).Value &lt;&gt; Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;).Value Then
  
  Cells(i, 25).Value = Cells(i, 28).Value
  
  ElseIf Cells(i, 23).Value = Worksheets(&quot;Products&quot;).Cells(15, 8).Value And Cells(i, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) And Cells(i + 1, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) And Cells(i + 2, 29).Value &lt;&gt; Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) Then
  
  Cells(i, 25).Value = Cells(i, 28).Value + Cells(i + 1, 28).Value
  
  ElseIf Cells(i, 23).Value = Worksheets(&quot;Products&quot;).Cells(15, 8).Value And Cells(i, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) And Cells(i + 1, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) And Cells(i + 2, 29).Value = Worksheets(&quot;Products&quot;).Range(&quot;B15:B36&quot;) Then
  
  Cells(i, 25).Value = Cells(i, 28).Value + Cells(i + 1, 28).Value + Cells(i + 2, 28).Value
  
    End If
   Next i
  </code></pre>
  <p>where cell (i,23) is the column with the codes and cell(15,8) is the cell with the fix product I am first working on to find on the second table. So if the first product belongs to the code on table 1 but the second and third product doesnt, then just write the same demand. if the first and second product belongs to the code and the third doesnt, then just sum the first and the second demand, and so on. I also tried with a for loop but it didnt work since it was evaluating cell by cell, and I need it to evaluate the whole range.</p>
  <p>I also tried using Vlookup so It would look for the product type of Table 1 on Table 2. So if the code on Table 1 = the code that belongs to the product on Table 1 then do x</p>
  <pre><code> For i = 14 To 76
  If Cells(i, 23).Value = Application.WorksheetFunction.VLookup( _
  Cells(i, 29).Value, Worksheets(&quot;Products&quot;).Range(&quot;B15:H36&quot;), 7, False) Then
      Cells(i, 25).Value = Cells(i,28).Value
  End If
  Next i
  </code></pre>
  <p>Where (I,23) is the code on table 1 (1007),  (i,29) is the product type on table 1 (H102), (i,25) is the consumption and (i,28) is the demand. This is only for the case where the rest of the products doesnt share the same code so there is no sum needed.</p>