title: Using Index Match In excel VBA
body: <pre><code>Dim DateValues As Variant
  Dim Shortfall As Variant
  Dim Region As Variant
  Dim Bucket As Variant
  
  Dim Shortfall_rng As Range
  Dim Region_rng As Range
  Dim Bucket_rng As Range
  Dim Datevalues_rng As Range
  
  Dim estring As Variant
  
  Dim StartCopying As Range
  
  
  Set StartCopying = Worksheets("Asia_All").Range("O4")
  
  Region = Worksheets("Asia_All").Range("O1").Value ' Value in cell O1 is "New York" (without Quotes)
  Bucket = Worksheets("Asia_All").Range("O2").Value ' Value in cell O2 is "&lt;!--1--&gt;0 - 1" (without Quotes)
  DateValues = Worksheets("Asia_All").Range("O3").Value ' Value in cell O3 is "2007-01-02" (without Quotes)
  
  
  
  Set Shortfall_rng = Worksheets("Asia_All").Range("$B$2:$B$142411")
  Set Bucket_rng = Worksheets("Asia_All").Range("$F$2:$F$142411")
  Set Datevalues_rng = Worksheets("Asia_All").Range("J$2:$J$142411")
  Set Region_rng = Worksheets("Asia_All").Range("$K$2:$K$142411")
  
  
  estring = Evaluate("Index(" &amp; Shortfall_rng.Address &amp; ", Match(" &amp; Bucket &amp; Region &amp; DateValues &amp; ", " &amp; Bucket_rng.Address &amp; " &amp; " &amp; Region_rng.Address &amp; "&amp;" &amp; Datevalues_rng.Address &amp; ", 0))")
  
  StartCopying.Value = estring
  </code></pre>
  
  <p>I am trying to use Index Match ,but I am getting #Value! error. Please note that it works absolutely fine if i manually enter the index match formula in the cell. This error appears only when i try to replicate it in excel vba.To give a brief,</p>
  
  <p><strong>Bucket_rng column contains a coulmn of multiple values of</strong>  </p>
  
  <pre><code> &lt;!--1--&gt;0 - 1
   &lt;!--2--&gt;1 - 2.5
   &lt;!--3--&gt;2.5 - 5
   &lt;!--4--&gt;5 - 10
   &lt;!--5--&gt;10+
  </code></pre>
  
  <p><strong>Shortfall_rng column contains a coulmn of multiple values like</strong></p>
  
  <pre><code>-4.69
  -3.94
  -53.94
  150.18
  -18.78
  -9.98
  -17.04
  -41.32
  0.0001
  </code></pre>
  
  <p><strong>Datevalues_rng column contains a coulmn of multiple values in yyyy-mm-dd format</strong> </p>
  
  <p>and </p>
  
  <p><strong>Region_rng column contains a coulmn of multiple values like</strong></p>
  
  <pre><code>China
  New York
  Australia
  Japan Continent
  </code></pre>