title: How to Find the n-th most similar value
body: <p>The goal is to find the two most similar operational days, and return their averages for the production measurements. This will help future operational planning. </p>
  
  <p>So I have a list of facilities and their operational data for the past 90 days. So each facility will have one row per day. This info is on its own worksheet, and is pulled in from an excel-based query. So essentially this tab "PDA" has the columns "Facility", "Date", and "Total Output". </p>
  
  <p>On the 2nd tab, "Analyzer", I have a similar set up. But its for future dates. What I want to do is take the "CPWTotal" (total output for the future dates) and find the two most similar days from the "PDA" tab and return those two "Total Outputs". I tried using a mash up of a index(small) and min(abs) function to find the smallest differences, but it didn't work nearly as well as I hoped. </p>
  
  <p>Below is the closest I've gotten, but the comparison to building number has been a major hang up. </p>
  
  <p>PDA!AB contains the historical output data, PDA!W contains the historical data building number, A3 contains the future plan building number, and C3 contains the future output forecast. </p>
  
  <pre><code>{=INDEX(PDA!$AB$2:$AB$10000,MATCH(($A3 = PDA!$W$2:$W$10000)*MIN(ABS(PDA!$AB$2:$AB$10000-$C3)),ABS(PDA!$AB$2:$AB$10000-$C3),0))}
  </code></pre>
  
  <p>I know the ()*Min() portion might be tripping me up, but my logic was that is would be 0*Min() if the building number was not a match, thus returning a 0 for that line of the match, and I have not been able to figure out a better approach. </p>
  
  <p>This isn't the last step, but its what I need to set up the index(match) to pull the rest of the operational data that I need to average out. </p>
  
  <p>I am also open to VBA solutions.</p>
  
  <p>EDIT: Sample Data:</p>
  
  <p>Tab 1: PDA Return (worksheet where I want the output to be returned) 1 and 2 are the columns where the closest and 2nd closest match will be returned.</p>
  
  <p>Bulding #   2016    CPW Forecast    1   2</p>
  
  <p>4505    29-Feb-16   1789<br>
  4505    01-Mar-16   2270<br>
  4505    02-Mar-16   2108<br>
  4505    03-Mar-16   2028<br>
  4505    04-Mar-16   1912<br>
  4600    29-Feb-16   1653<br>
  4600    01-Mar-16   2264<br>
  4600    02-Mar-16   2232<br>
  4600    03-Mar-16   2013<br>
  4600    04-Mar-16   1876<br>
  4650    29-Feb-16   1799<br>
  4650    01-Mar-16   2254<br>
  4650    02-Mar-16   2181<br>
  4650    03-Mar-16   1902<br>
  4650    04-Mar-16   1910        </p>
  
  <p>Tab 2 (PDA) contains the info I want to search for:</p>
  
  <p>Building #  2016    CPW Total Production</p>
  
  <p>4505    29-Feb-16   1799<br>
  4505    01-Mar-16   2250<br>
  4505    02-Mar-16   2118<br>
  4505    03-Mar-16   2025<br>
  4505    04-Mar-16   1914<br>
  4600    29-Feb-16   1654<br>
  4600    01-Mar-16   2262<br>
  4600    02-Mar-16   2231<br>
  4600    03-Mar-16   2016<br>
  4600    04-Mar-16   1826<br>
  4650    29-Feb-16   1789<br>
  4650    01-Mar-16   2244<br>
  4650    02-Mar-16   2161<br>
  4650    03-Mar-16   1912<br>
  4650    04-Mar-16   1920</p>
  
  <p>I don't know how to upload a sample excel file but you should be able to just copy/paste those tables. </p>