Solution 1:

I think what you want is something like:

=INDEX(B:B,MATCH(C2,A:A,0))  

I should mention that MATCH checks the position at which the value can be found within A:A (given the 0, or FALSE, parameter, it looks only for an exact match and given its nature, only the first instance found) then INDEX returns the value at that position within B:B.

Solution 2:

=IF(ISNA(INDEX(B:B,MATCH(C2,A:A,0))),"",INDEX(B:B,MATCH(C2,A:A,0)))

Will return the answer you want and also remove the #N/A result that would appear if you couldn't find a result due to it not appearing in your lookup list.

Ross

Solution 3:

All you have to do is write an IF condition in the column d like this:

=IF(A1=C1;B1;" ")

After that just apply this formula to all rows above that one.