Selected Answer
You might use the VLOOKUP() function for this, e.g.
= VLOOKUP($A2,'Sheet1'!$A:$B, 2)
Interpreted, the formula says "Look for the value in $A2 anywhere in the first column of the range 'Sheet1'$A:$B and return the value in column 2 from the row where you found it. Excel will drop the apostrophes embracing the sheet name if that name doesn't contain any spaces (as is the case in the above example).
The formula will return a N/A error if the value isn't found. To catch the error you might embed the formula in an IFERROR() function, like this.
=IFERROR(VLOOKUP($A2,'Sheet1'!$A:$B, 2),"No Match")
You can replace the "no match" string with any string you like including a null string ("").
Looking in all the 1048576 rows of the range $A:$B takes a little time and will slow down your worksheet if there are many evaluations to do. Therefore it would be better practice to specify a named range. If you feel one is too much trouble and the other too slow you might limit the range to something like 'Sheet'!$A$2:$B$1000.