title: excel vba - search for a value in an array
body: <p>How do I select a value from an array, and write it on a cell, based on a condition?</p>
  
  <p>I have this:</p>
  
  <pre><code>Dim Material As Variant
  
  Dim Material1 As Variant
  Material1 = Array("16", "20", "26", "32", "40", "50", "63", "70")
  
  Dim Material2 As Variant
  Material2 = Array("12", "16", "22", "28", "34", "42", "58", "65")
  
  Dim Rows as Long
  For Rows = 9 to lastrow
  
  If Range("P"&amp;Rows).Value &lt;&gt; 0 then
  
  If Range("W"&amp;Rows).value = Material1 then
  Material = Material1
  'material can be Material2 also, but I would do the same IF
  
  
  If Range("Z"&amp;Rows).Value &gt; 2 then
  Range ("X"&amp;Rows).value = ' this is where I fail, I want to write here a value from Material1 (looping through position 1, 2, etc...)that does the opposite to my condition, i.e. &lt;2 (this is related with formulas in the sheet)
  </code></pre>
  
  <p>I didn't forget to "end" the IFs and to move to "Next Rows"</p>