title: Looking up values in a sliced 2D array - Excel VBA
body: <p>I have a table in Excel which looks like this:</p>
  
  <pre><code>        A         C         D
  0       0.00%     0.00%     0.00%
  473     0.00%     0.00%     3.40%
  641     13.80%    13.80%    10.40%
  646     13.80%    13.80%    10.40%
  3337    13.80%    13.80%    13.80%
  3454    13.80%    13.80%    13.80%
  </code></pre>
  
  <p>The function is of the form myFunction(x,y,thresholds_table) where x can be A, C or D and thresholds_table is the whole table. The function involves looking up values from one of the three columns depending on the value of x. If I manually specify a column (e.g. set r=2), the function works fine, but I can't work out how to choose the column.</p>
  
  <p>I'm new to VBA and used to programming languages with much better support for array slicing, so perhaps I'm going about this the wrong way. The code I'm trying to use is:</p>
  
  <pre><code>Dim rates As Range
  
  rates = Application.WorksheetFunction.Index(thresholds_table, 1, 0)
  r = Application.WorksheetFunction.Match(x, rates)
  </code></pre>
  
  <p>using the INDEX function to select the top row, then Match to find the column index. However, it always returns a VALUE error.</p>
  
  <p>This seems like it must be simple, but I've done a lot of googling and not been able to find an answer!</p>
  
  <p>EDIT:</p>
  
  <p>Sorry, I wasn't entirely clear: the "y" argument isn't used in this part of the function. Basically I just need a function which returns 2 if x=A, 3 if x=C or 4 if x=D. I don't want to put it in directly like that though since the A/C/D may change in the future, so it needs to use whatever the column headings are.</p>