title: Excel formula - index match address indirect rank.avg
body: <p>Need help crafting formula.</p>
  
  <p>I have data in SQL, exporting to excel. In excel have formula's to determine rank of parts based on values in a certain week in a range/array. The columns are weekly buckets, and each time I export, the weekly buckets change (no dates in the past). I would like the rank to remain fixed a specific date, but each export per week the specific column comes inward by 1 column.</p>
  
  <p>I need an excel formula which finds the position of the specific value in the weekly bucket array. After this is determined, I need another formula to convert this column address (e.g. "AX") to be referenced by the rank.avg function.</p>
  
  <p>I am guessing this is some combination of match, index, cell, indirect, &amp; rank.avg. So far I have tried just the first part and cannot get cell("col",(index(match))) to return the letter values of the column it is in. It returns the # of the column instead.</p>
  
  <p>Here is example</p>
  
  <pre><code>  A       B        C         D
  1 Part    Rank(C)  11/28/17  12/04/17
  2 Part A  1        $100      $100    
  3 Part B  2        $50       $125    
  4 Part C  3        $25       $200    
  </code></pre>
  
  <p>Column B has the rank formula which is determining the rank of the parts (A,B,C) from column C. Column C is determining from hardcoding go to the value 11/28/17.</p>
  
  <p>I am stuck at =CELL("address",INDEX(C1:D1,1,MATCH(43067,C1:D1,0))). This returns $C$1 when I just want $C or just C. Afterwards I intend to use this with indirect to be able to pass it through the rank function. Any assistance would be greatly appreciated where someone can fill the gap on the formula or come up with an easier way to do this.</p>
  
  <p>Nick</p>