title: Need Help Using INDEX and MATCH with a Dynamic Named Range
body: <p>Hi I have 2 ListBox's (Purchase_Select_Debtor) &amp; (Purchase_Select_Quantity) on a Userform with a txtBox for Price (txtPrice). </p>
  
  <p>The Code uses Index and Match to return a result based on the Debtor and Quantity Selected.</p>
  
  <p>Below is my current code which works fine, but every time I add a new Debtor I have to alter the code to change the Physical Range.  </p>
  
  <p>I would like to set it up using Dynamic Named Ranges so as I add new Debtors the Index/Match Function will still return a Result.</p>
  
  <pre><code>Temp = Application.Index(Sheets("Price_list").Range("A1:I22"), _
  Application.Match(Purchase_Select_Debtor.Value, Sheets("Price_list").Range("A1:A22"), 0), _
  Application.Match(Purchase_Select_Quantity.Value, Sheets("Price_list").Range("A1:I1"), 0))
  
      txtPrice.Value = FormatCurrency(Expression:=Temp, _
      NumDigitsAfterDecimal:=2)
  </code></pre>
  
  <p>I tried this but it didn't work; Type Missmatch Run time Error 13</p>
  
  <pre><code>Temp = Application.Index(Range("Price_list_Table"), _
  Application.Match(Purchase_Select_Debtor.Value, Range("Price_list_Debtor_ADD"), 0), _
  Application.Match(Purchase_Select_Quantity.Value, Range("Price_list_Quantity_ADD"), 0))
  
      txtPrice.Value = FormatCurrency(Expression:=Temp, _
      NumDigitsAfterDecimal:=2)
  </code></pre>
  
  <p>Debugger highlights these 2 lines of code;</p>
  
  <pre><code>    txtPrice.Value = FormatCurrency(Expression:=Temp, _
      NumDigitsAfterDecimal:=2)
  </code></pre>
  
  <p>The Value of Temp = ""</p>
  
  <p>The chart it's using as a refrence is this;</p>
  
  <p><a href="http://i19.photobucket.com/albums/b152/mantricorb/Chart.jpg" rel="nofollow">http://i19.photobucket.com/albums/b152/mantricorb/Chart.jpg</a></p>
  
  <p>And the Dynamic Named Ranges are as Follow;</p>
  
  <p>Price_list_Table
  =OFFSET(Price_list!$A$1,0,0,COUNTA(Price_list!$A:$A),9)</p>
  
  <p>Price_list_Debtor_ADD
  =OFFSET(Price_list!$A$1,0,0,COUNTA(Price_list!$A:$A),1)</p>
  
  <p>Price_list_Quantity_ADD
  =OFFSET(Price_list!$A$1:$I$1,0,0)</p>
  
  <p>I assume there is something wrong with the Dynamic Named Ranges as it doesn't return a result, any help will be muchly appreciated.</p>