I have found that the easiest way to use VLOOKUP and get the syntax right is as follows:
For the sake of this exercise start off by putting the data of Codes and Destinations in two columns in sheet one, say in G1 to 10 and H1 to 10. It is essential that the vertical list of destination codes in column G is in ascending alphabetical order (do this by highlighting cells G1 to H10 and using the Data Sort tool to sort column G). Check that the destination names still correspond correctly with the codes.
Now type the destination code into cell A1
a) Highlight cell B1. Click on Insert---Function---Lookup & Reference---VLOOKUP
b) Fill in the boxes as follows: in the ‘Lookup value’ box enter A1 (no $ sign); in the ‘Table Array’ box type G$1:H$10 (the $ signs in the cell names ensure that just those cells are referenced for every lookup performed in column B); in the ‘Col Index No’ box type 2. Leave the ‘Range Lookup’ box blank.
c) Test by inserting a code into cell A1 and ensure that the correct destination name comes up in B1.
d) Copy the contents of B1 down column B as many times as you wish. Every time you enter a code in column Ax the destination should appear in Bx.
e) Highlight the table G1:H1. You can then cut and past it to either somewhere more inconspicuous in sheet one, or if you wish, in sheet two. The VLOOKUP function you have entered in B1 will adjust appropriately.
Think I have understood your query correctly and hope this helps. Send me a PM if I can help further.