1. Your file contains links to outside sources.

2. only 1 entry in sheet 2 matches anything on sheet 1

3. Try this in sheet 2 ...
D2 =VLOOKUP(A2,'Gavin query for HMC'!$A$2:$C$11,3,0)
copied down