I have been fighting with this problem for an hour. Finally read that the leading apostrophe instructs Excel to "align left". I had tried using the CLEAN function, but it had not worked. This time I used the CLEAN function and then, while the formula was still in the cells, I highlighted and used the "Align Left" command. Next I copied and pasted as values and VOILIA!! the dratted leading apostrophe was gone! WOOT WOOT!!! My vlookup now works perfectly.
d.c.1