Formula excludes displayed leading zeros

[wonderfulsomebody123]

{=LOOKUP(2,1/("0"=LEFT(B:B,1)),A:A)}

The above formula doesn't catch on to the leading zeros that are displayed in B:B cells. I understand that making B:B cells with custom formatting as 00 only displays the leading zeros for me to see but I want to make Excel include those zeros when using formulas. How?

I tried {=LOOKUP(2,1/("0"=LEFT(TEXT(B:B,1)),A:A)} but that doesn't work.

Please help, thank you!

 

[Tom.Jones]

Upload picture with your data and show expected result.

Select Range("B1:B100000") then in tab NUMBER choose TEXT

 

[Fluff]

How about

=LOOKUP(2,1/(B2:B100<>"")*("0"=LEFT(TEXT(B2:B100,"00"),1)),A2:A100)