title: Excel - find smallest difference between value and list, and associated index
body: <p>I have 2 lists of values in Excel (B2:B5002 and K2:26). For each of the values in column K, I need to find the closest value in column B, and then return the corresponding number in column A.</p>
  
  <p>I have worked out how to find the smallest difference between the values in columns B and K, but I can't work out how to now get the corresponding value from column A.</p>
  
  <p>Here is an example of what I have already:</p>
  
  <pre><code>    A   B      K   
      1   7.55   9
      2   9.33   3
      3   1.05   7
      4   2.38
      5   6.22
  </code></pre>
  
  <p>What I would like is for column L to return 2, 4, 1 (the values in A that correspond to the closest values in B). So far, I have used this code in column L to find the smallest difference:</p>
  
  <pre><code>    =MIN(ABS($B$1:$B$5-$L1))
  </code></pre>
  
  <p>and then I press CTRL+SHFT+ENTER to get it to be an array formula and then drag it into the other rows of column L. I don't know what to do to get the numbers from A that correspond to these differences! Can anyone help out?</p>
  
  <p>I just thought I would mention that I have done this fine in Matlab, but I am trying to do the same thing in Excel for a student who can't use Matlab. They could do it manually, but I am trying to save them some time.</p>