title: VLOOKUP based conditional formatting highlights the cell above the intended one
body: <p>I'm trying to apply conditional formatting to a column in Excel using the "Use a formula to determine which cells to format" feature. </p>
  
  <p>In Sheet 1 have a list of names in column A with corresponding reference numbers in column B. In Sheet 2 I have a longer list of reference numbers in column C. I want the conditional formatting rule to look up the reference numbers in Sheet 1 against those in the Sheet 2 and highlight the cell where they is an overlap.</p>
  
  <p>I have found an apparent solution to this using a vlookup as the formula:</p>
  
  <pre><code>=$B2=VLOOKUP($B2,Sheet2!$C:$C,1,FALSE)
  </code></pre>
  
  <p>This works in that cells in Sheet 1 have the correct formatting applied, but all the cells have been shifted up by one (<a href="http://i.stack.imgur.com/sIhzm.png" rel="nofollow">image</a>). I have copied the highlighted reference numbers out and done a lookup against the reference numbers in sheet 2 to double-check and the top one highlighted isn't in Sheet 2 but the one below the filled cells is (<a href="http://i.stack.imgur.com/7AdD4.png" rel="nofollow">image</a>).</p>
  
  <p>I have tried replacing the vlookup in the conditional formatting with an INDEX/MATCH function in case there was a problem with the VLOOKUP but the result was the same: <code>=$B2=INDEX(Sheet2!$C:$C,MATCH($B2,Sheet2!$C:$C,0))</code></p>
  
  <p>I've tried removing the spaces between the xx and numbers in the references based on a different stackoverflow answer, but no luck. I've also tried changing the format of the cells from General to Text with the same result.</p>
  
  <p>Any ideas would be appreciated!</p>