Solved

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

Posted on 2016-10-28
23 Views
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
Question by:agwalsh

LVL 33

Assisted Solution

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

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 48

Accepted Solution

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

Author Comment

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 48

Expert Comment

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

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

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.