Need to get data from one sheet to another in excel - vlookup brings wrong value

Question

I have an excel file having two sheets like below and I need to use lookup to bring a value from one sheet to another:

Sheet1:

Name   Address
A1     Address1
A2     Address2
A3     Address3
...

Sheet2:

Code   Name   
code1   A1     
code2   A2     
code3   A3     
...
code17  A17    
...

I want to add Code column to Sheet1. So on Sheet1, I use:

=LOOKUP(B2,Sheet2!B$2:B$798,Sheet2!A$2:A$798) //Returns wrong code

and

=VLOOKUP($A2,Sheet2!$A$2:$B$798,1,FALSE) //Returns #N/A

and

=VLOOKUP($A2,Sheet2!$A$2:$B$798,1,TRUE) //Returns wrong code

Note that the Code, Name and Address columns are all unique.