title: Index/Match Function Lookup Array with multiple criteria
body: <p>I looked but could not find a valid response for this specific circumstance.</p>
  
  <p>I need to write a macro that searches a table for a specific Name (Column A), Material (Column B), and Color (Column C) combination then returns the price from Column D.</p>
  
  <p>I can enter in the formula (array)</p>
  
  <pre><code>=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!G1:G5552=C1),0),4)
  </code></pre>
  
  <p>Where A1 has the Name of the item I am searching for, B1 has the Material and C1 has the Color and the value returns just fine. However, I have set it up to quickly choose between Name/Material/Color and I want to be able to pull the prices quickly so I am trying to write a Macro to run when I click a button.</p>
  
  <p>The problem I am encountering is transposing the MATCH(1,(Sht1!A1:A5552=A1)<em>(Sht1!B1:B5552=B1)</em>(Sht1!C1:C5552=C1),0)  part into VBA. I am trying to avoid just using the record function because it spits a .ForumlaArray out in R1C1 format that is difficult to interpret and update in the future.</p>
  
  <p>I have tried </p>
  
  <pre><code>Application.Index(Sht1.Range("A1:D5552"), _
    Application.Match(1, Sht1.Range("A1:A5552") = Range("A1") &amp; _
    "*" &amp; Sht1.Range("B1:B5552") = Range("B1") &amp; "*" &amp; Sht1.Range("C1:C5552") = _
    Range("C1"), 0), 4)
  </code></pre>
  
  <p>But get a Type Mismatch</p>
  
  <p>I also tried putting in the .ForumlaArray but get an "unable to set FormulaArray property of the range class" error (because it needs R1C1 Format)</p>
  
  <pre><code>"=INDEX(Sht1!A1:D5552,MATCH(1,(Sht1!A1:A5552=A1)*(Sht1!B1:B5552=B1)*(Sht1!C1:C5552=C1),0),4)"
  </code></pre>
  
  <p>I have variables set up a little differently but I am trying to simplify these examples to make it easier to interpret.</p>