VLOOKUP based conditional formatting highlights the cell above the intended one
<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>