title: Closest number with conditions applied
body: <p>I want to find the closest number to a number I input from a list of number depending on certain conditions.  Currently I'm</p>
  
  <pre><code>=SMALL($C$3:$C$16,COUNTIFS($C$3:$C$16,"&lt;"&amp;$E3)+1)
  =LARGE($C$3:$C$16,COUNTIFS($C$3:$C$16,"&gt;"&amp;$E3)+1)
  </code></pre>
  
  <p>$E3 is the number I input. C3:c16 is the range of numbers I'm looking to.  I take the ABS value of both results and which ever is closest to E3 I take.  This works fine.  However I want to introduce a condition.  If any cell in Range $B3:$B16 contains "Apple" (as example) I want to exclude the number in column C from my calculation.  Any help would be really appreciated.</p>
  
  <p>Ciaran.</p>