Vlookup with left - all formatted as text - getting N/A - what am I missing

hi Folks
Am attaching a file with a vlookup (with a left function). The left function works fine (cell E2) but it is not working with the Vlookup. I have formatted both columns as text...so what am I missing? Thanks.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Rgonzo1971Connect With a Mentor Commented:
NorieConnect With a Mentor VBA ExpertCommented:
Try this.

contactkarthiConnect With a Mentor Commented:
Left gives back you a string you need to use value function to convert it to a number. That will fix the issue.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

agwalshAuthor Commented:
Thanks for all that..but why won't converting both sets of data to text work?  Surely left is a text function - the data in A2:A26 is text. The data in H2:H26 is text....
@Rgonzo1971 - what does the -- in front of the Left do?
Even if it's formatted as text, Excel uses it as a number
only if the number would be preceded by a apostrophe
would Excel treat it as text

-- does the same work as value convert text to number
agwalshAuthor Commented:
Good to know that preceding a left() function with -- gets over the text issue with vlookups..
All Courses

From novice to tech pro — start learning today.