Force Excel 2007 to treat all values in a column as text

If changing the format of the column is not an option, it's helpful sometimes to create another column that's 'vlookup friendly' and leave your main column alone.

This is a trick I've used a few times:

Say your 'mixed' column is column A.

In column B, enter the formula:

 =CONCATENATE(A1)

or as Jean-François pointed out in a comment, the shorter version:

 =A1 & ""

And drag it down for to the bottom row.

Column B will be all strings. The VLookup can then use column B.

Under the Data Tab, open the Text to Columns wizard and set the Column data format to Text. The destination cell can be set to the original data cell, but it will replace the original formatting with text formatting. Other aspects of formatting e.g. Bold, color, font, etc. are not changed using this method.