How do I use VLOOKUP to search for text?

I have 3 worksheets with information. Sheet one has names in code (text) and
sheet 2 has the code and the name. I am trying to use LOOKUP to place the
names on sheet 3 instead of the code.