Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

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.
vlookup_with_left.xlsx
0
agwalsh
Asked:
agwalsh
3 Solutions
 
NorieVBA ExpertCommented:
Try this.

=VLOOKUP(VALUE(LEFT(A2,3)),$H$2:$I$26,2,FALSE)
0
 
contactkarthiCommented:
Left gives back you a string you need to use value function to convert it to a number. That will fix the issue.
0
 
Rgonzo1971Commented:
or
=VLOOKUP(--(LEFT(A2,3)),$H$2:$I$26,2,0)
0
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?
0
 
Rgonzo1971Commented:
Even if it's formatted as text, Excel uses it as a number
only if the number would be preceded by a apostrophe
'857
would Excel treat it as text

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now