Having issues with VLookup of merge and center values

[anirudh2012]

I am having following data where I have shop numbers and street number(merged and centered) :

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl72, width: 192, colspan: 3"]Shop number[/TD]
[TD="class: xl65, width: 64"]Street number[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1109393[/TD]
[TD="class: xl66, width: 64"]Pending[/TD]
[TD="class: xl66, width: 64"]All[/TD]
[TD="class: xl75, width: 64, align: right"]1679252[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1121035[/TD]
[TD="class: xl66, width: 64"]Pending[/TD]
[TD="class: xl66, width: 64"]All[/TD]
[TD="class: xl75, width: 64, align: right"]1683992[/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1124376[/TD]
[TD="class: xl66, width: 64"]Pending[/TD]
[TD="class: xl66, width: 64"]All[/TD]
[TD="class: xl66, width: 64, align: right"]1696966[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1124534[/TD]
[TD="class: xl66, width: 64"]Pending[/TD]
[TD="class: xl66, width: 64"]All[/TD]
[TD="class: xl75, width: 64, align: right"]1697256[/TD]
[/TR]
[TR]
[TD="class: xl70"] [/TD]
[TD][/TD]
[TD="class: xl71"] [/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl69"] [/TD]
[/TR]
</tbody>[/TABLE]


In another sheet, i have shop numbers and I want street number corresponding to it with the help of Vlookup.
But the problem is ..VLookup is giving error values. i will share the result and my formula :

Formula : =VLOOKUP(A2,A:D,4,0)

Result [TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1109393[/TD]
[TD="width: 64, align: right"]1679252[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1121035[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1124376[/TD]
[TD="align: right"]1683992[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1124534[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

Set the horizontal alignment for all Shop numbers to GENERAL. make sure they are either all left aligned (text) or all right aligned (number values).