Solved

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

Posted on 2016-10-28
6
31 Views
Last Modified: 2016-10-28
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
Comment
Question by:agwalsh
6 Comments
 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 41863523
Try this.

=VLOOKUP(VALUE(LEFT(A2,3)),$H$2:$I$26,2,FALSE)
0
 
LVL 17

Assisted Solution

by:contactkarthi
contactkarthi earned 125 total points
ID: 41863609
Left gives back you a string you need to use value function to convert it to a number. That will fix the issue.
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41863612
or
=VLOOKUP(--(LEFT(A2,3)),$H$2:$I$26,2,0)
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:agwalsh
ID: 41863624
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
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41863636
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
 

Author Closing Comment

by:agwalsh
ID: 41863685
Good to know that preceding a left() function with -- gets over the text issue with vlookups..
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question